[[20160719184816]]　『ユーザーフォーム　エクセルの値をTextBoxに表示普x（ひなの）　ページの最後に飛ぶ

『ユーザーフォーム　エクセルの値をTextBoxに表示方法』（ひなの）

 いつもお世話になっております。
 今回もまた検索でエラーになり原因がわからず質問に来ました。
 また、教えていただけると大変助かります。

 以前はフィルターを使っての検索方法を教えていただいたんですが。

 Q1.今回はApplicationで検索を試したのですが、
 　エラーで値が検索されないのです。原因がわかりましたら教えてください。よろしくお願いします。

 ▼フォームの使用
 TextBox1 　検索値（商品NO）
 TextBox2～TextBox10　が　検索結果を表示してます。

 ▼コード
 Private Sub TextBox1_Change()

  Dim Ap As Long
 ' Dim ListNo As Long
  Dim Ctrl As Control

    For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then _
            Ctrl.Value = ""
    Next Ctrl

     With Worksheets("商品")

       Ap = Application.Match(TextBox1.Value, Worksheets("商品").Range("B:B"), 0)

       'lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
       'i =  'TextBox1と同じ商品NOを検索

        If IsNumeric(Ap) Then
            TextBox2.Value = .Cells(Ap, 2).Value 'NO
            TextBox3.Value = .Cells(Ap, 3).Value '商品名
            TextBox4.Value = .Cells(Ap, 4).Value '容量
            TextBox5.Value = .Cells(Ap, 7).Value '数量
            TextBox6.Value = .Cells(Ap, 10).Value '重量
            TextBox7.Value = .Cells(Ap, 11).Value '価格
            TextBox8.Value = .Cells(Ap, 12).Value '原価
            TextBox9.Value = .Cells(Ap, 13).Value '仕入数

     Else  '↓値が有るときは
        MsgBox "新規データです。"
     End If

     End With

 End Sub

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 MATCH をかける前に

    For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then _
            Ctrl.Value = ""
    Next Ctrl

 ここで　TextBox の値をすべて "" にしていますね？
 当然 TextBox2 の値も "" になっているわけですが

 その　"" である TextBox2.Valueで検索していますから具悪いのではないですか？

 ところで、 TextBox2 の値で検索しなおすわけですから TextBox1_Change ではなく TextBox2_Change だと思うのですが？

（β） 2016/07/19(火) 19:07

 （β）いつもありがとうございます。今回もよろしくお願いします。

 早速ドジ発覚ですね　(￣￣￣∇￣￣￣;

 ＞ところで、 TextBox2 の値で検索しなおすわけですから TextBox1_Change ではなく TextBox2_Change だと思 うのですが？

 すみません間違えです。

 指摘していただいた箇所直したんですが、やはり
 Application＝0のままです。

 これが原因でしょうか？
 検索値は商品番号です。「A01」「B05」とかです。

 でもTextBox1に「A」と入力後すぐエラーになります。数字を入れる暇がありません。
 これはどうなっているのでしょうか？

 Private Sub CommandButton4_Click()こちらは問題なく表示されるんですが。
 ただ、ほかのところが・・・・

　Private Sub TextBox1_Change()

  Dim Ap As Long

     With Worksheets("商品")

       Ap = Application.Match(TextBox1.Value, Worksheets("商品").Range("B:B"), 0)

       'lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row

        If IsNumeric(Ap) Then
            TextBox2.Value = .Cells(Ap, 2).Value 'NO
            TextBox3.Value = .Cells(Ap, 3).Value '商品名
            TextBox4.Value = .Cells(Ap, 4).Value '容量
            TextBox5.Value = .Cells(Ap, 7).Value '数量
            TextBox6.Value = .Cells(Ap, 10).Value '重量
            TextBox7.Value = .Cells(Ap, 11).Value '価格
            TextBox9.Value = .Cells(Ap, 12).Value '原価
            TextBox10.Value = .Cells(Ap, 13).Value '仕入数

     Else  '↓値が有るときは
        MsgBox "新規データです。"
     End If

     End With

 End Sub

 Private Sub CommandButton4_Click()
 Dim Ap As Long

     With Worksheets("商品")

       Ap = Application.Match(TextBox1.Value, Worksheets("商品").Range("B:B"), 0)

       'lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row

        If IsNumeric(Ap) Then
            TextBox2.Value = .Cells(Ap, 2).Value 'NO
            TextBox3.Value = .Cells(Ap, 3).Value '商品名
            TextBox4.Value = .Cells(Ap, 4).Value '容量
            TextBox5.Value = .Cells(Ap, 7).Value '数量
            TextBox6.Value = .Cells(Ap, 10).Value '重量
            TextBox7.Value = .Cells(Ap, 11).Value '価格
            TextBox9.Value = .Cells(Ap, 12).Value '原価
            TextBox10.Value = .Cells(Ap, 13).Value '仕入数

        Else  '↓値が有るときは
            MsgBox "新規データです。"

     End If

     End With
End Sub

（ひなの） 2016/07/19(火) 19:36

 TextBox1_Changeなら問題が発生し、CommandButton4_Clickではうまくいくという理由は１つだけ。
 処理コードは全く同じですから、違うのは、検索値として何を与えているかのみ。

 ユーザーフォームのTextBoxやComboBoxのChangeイベントは、扱いがきわめて厄介なものです。

 たとえば ABC というものをいれて MATCHさせるとします。
 おそらく TextBox1.Value に ABC という値が入った形で、TextBox1_Changeが実行されると、そう思っていますよね？

 でも、違います。
 TextBox に対してAとタイプされた瞬間に動きます。
 次に B を入れたときに AB という値で動きます。次に C をいれた時点で ABC という値で動きます。

 つまり ABC のうち、A がタイプされたとたんに TextBox1.Value が A という内容で動いてしまいます。
 で、A で検索する。見つからない。なので【実行時エラー】になるはずです。（後述）

 Changeイベントで対応するなら、見つからない場合は、入力途中だと認識して、セットすべき値をクリアして
 あとは何もしないで抜ける。

 でも、そうすると、最終的にABCで見つからなかった場合も、マクロ内でのMsgBoxによるメッセージもだせないということになります。

 CommandButtonクリックによる処理の場合は、ちゃんと ABC と入れた後にクリックするわけですから、検索ができるということなんです。

 TextBoxイベントを使うなら、Changeイベントではなく、ABCと入力して確定（EnterやTabなどの押下）させたときに発生する
 BefopreUpdateやAfterUpdateといった Exit系のイベント処理をする必要がありますね。

 次に、【実行時エラー】の件。

 MATCHで検索値がない場合、シート上では #N/A になる局面ですね。
 この場合、WorksheetFunction.Match を使うと【実行時エラー】になり、マクロが中断します。
 それを避けるために Application.Match を使っているわけですね。こちらのほうは、マクロ中断することなく
 戻り値として『エラー値』が返されます。（検索成功すれば数値になりますね）

 ここまではいいのですが、問題は　Dim Ap As Long　。
 Long型変数にエラー値を格納しようとすると、【型が違います】という実行時エラーになります。
 ですから、エラー値であっても受け入れることができる　Variant型で、Dim Ap As Variant として規定します。
 そうすると、実行時エラーにはならず、If IsNumeric(Ap) Then　が真ではないので、Else にいって
 MsgBox "新規データです が実行されます。

 ★ただし、Variant型で受けて実行時エラーを回避したとしても ABC の A だけで動いてしまうことの対処は
 　必要ですね。

（β） 2016/07/20(水) 07:02

 ありがとうございます。

 Dim Ap As Long　と
 Dim Ap As Variant　てこんなに違いがあるんですね。

 Changeイベントもこのような落とし穴があるとは知らなかったです。
 使い勝手によってここら辺を気を付けなければならないのですね。
 大変勉強になりました。

 下記を直したら使用することができました。

 IF関数も問題なく作動しています。

 他の問題が起こらないことを祈って、ほんとにありがとうございました。

 Private Sub TextBox1_AfterUpdate()

  Dim Ap As Variant

     With Worksheets("商品")

       Ap = Application.Match(TextBox1.Value, Worksheets("商品").Range("B:B"), 0)

       'lRow = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row

        If IsNumeric(Ap) Then
            TextBox2.Value = .Cells(Ap, 2).Value 'NO
            TextBox3.Value = .Cells(Ap, 3).Value '商品名
            TextBox4.Value = .Cells(Ap, 4).Value '容量
            TextBox5.Value = .Cells(Ap, 7).Value '数量
            TextBox6.Value = .Cells(Ap, 10).Value '重量
            TextBox7.Value = .Cells(Ap, 11).Value '価格
            TextBox9.Value = .Cells(Ap, 12).Value '原価
            TextBox10.Value = .Cells(Ap, 13).Value '仕入数

     Else  '↓値が有るときは
        MsgBox "新規データです。"
     End If

     End With

 End Sub
（ひなの） 2016/07/20(水) 08:31

コメント返信：

[ 一覧(最新更新順) ]

[[20160719184816]] 『ユーザーフォーム エクセルの値をTextBoxに表示普x（ひなの） ページの最後に飛ぶ

[[20160719184816]]　『ユーザーフォーム　エクセルの値をTextBoxに表示普x（ひなの）　ページの最後に飛ぶ