皆様こんにちは、ノグチです。
エクセルのユーザーフォームを作って、使ってみるまでを目標に、各コントロールやプロパティ、イベントなどを連載記事でご紹介しています。
前回は、Initializeイベントで、ユーザーフォームを開いたら、ワークシートの値をユーザーフォーム上のテキストボックスに表示する方法をご紹介しました。
今回は、テキストボックスに入力した値をワークシートから検索して、関連する値を別のテキストボックスに表示する方法をご紹介していきます！
前回までのおさらい
前回までに作成したユーザーフォームがこちら。
今回は、フォーム上にあるふたつのテキストボックスを連動させていきますよ。
Valueプロパティでテキストボックスの値を操作する
テキストボックスの値は、前回記事でご紹介した通り、Valueプロパティで操作できるのでしたね。
別々のテキストボックスを操作するといっても、Valueプロパティにセットされた値を使って、別のテキストボックスのValueプロパティに値をセットするだけと考えれば、難しく考えることはありません。
ただふたつのテキストボックスのValueプロパティの値を操作すればよいだけなのです。
テキストボックスに入力した値からテーブルの値を検索する
今回は、こちらのテーブルを使って、値の検索をしてみます。
ユーザーフォーム上の「品目」のテキストボックスに、テーブルの「品目」にある値を入力すれば、入力値をテーブルから検索して、マッチするレコードの「生産地」の値を、ユーザーフォーム上の「生産地」のテキストボックスに表示させる、というのが今回の趣旨です。
検索用のコードを作る
早速、テキストボックスに入力した値をワークシートから検するコードを作っていきましょう。
ワークシート上から特定の値を検索する方法は、以前の記事でお伝えした方法が使えます。
検索結果は、表示したいテキストボックスのValueプロパティにセットするだけでokです。
検索の結果取得できた値を、「生産地」のテキストボックスにセットしてみましょう。
テキストボックス 「生産地」には、「TextRegion」というオブジェクト名をつけてありますので、こんなコードになります。
Dim myRange As Range
Dim rngSearch As Range
Set myRange = ActiveSheet.Range("A1:B7")
Set rngSearch = myRange.Find(What:=TextItem.Value, LookAt:=xlPart)
If Not rngSearch Is Nothing Then
'ヒットした値を生産地テキストボックスにセット
TextRegion.Value = ActiveSheet.Cells(rngSearch.Row, rngSearch.Column + 1).Value
End If
検索に使うイベント
さて、上で作成したコードを、テキストボックスのイベントプロシージャ内に差し込んでいきましょう。
テキストボックスのイベントは、以前の記事でいくつかお伝えしました。
今回はテキストボックスに入力された値を使いたいので、以前の記事の中でご紹介したイベントだと、ChangeイベントかExitイベントが使えそう、と思いますよね。
しかし、Changeイベントはテキストボックスに1文字入力される度に実行されてしまいますし、Exitイベントは、テキストボックスに入力した値で検索をしたいのに、そのテキストボックスからフォーカスを外さないと実行されません。
検索には不向きですよね。
AfterUpdateイベントで値を検索する
そこで今回は、テキストボックスのAfterUpdateイベントを使ってみましょう。
AfterUpdateイベントは、テキストボックスの値が変更されたあと、テキストボックスからフォーカスが外れると実行されるイベントです。
Exitイベントとの違い
テキストボックスからフォーカスが離れる時に実行されるなら、AfterUpdateイベントとExitイベントは同じじゃないの？と思われるかもしれません。
Exitイベントの場合、テキストボックスからフォーカスが外れて、別のコントロールにフォーカスが移動する場合に実行されます。
一方、AfterUpdateイベントは、テキストボックスのValueプロパティの値が変更されて、フォーカスがテキストボックスから離れる時に実行されます。
Exitイベントは、Valueプロパティが変更されたかどうかは関係なく実行されることになりますね。
AfterUpdateイベントで検索した値を別のテキストボックスに表示する
それでは、上でご紹介した検索のコードを、AfterUpdateイベントのイベントプロシージャに差し込んでみましょう。
コードの全体はこのようになります。
Private Sub TextItem_AfterUpdate()
Dim myRange As Range
Dim rngSearch As Range
Set myRange = ActiveSheet.Range("A1:B7")
Set rngSearch = myRange.Find(What:=TextItem.Value, LookAt:=xlPart)
If Not rngSearch Is Nothing Then
'ヒットした値を生産地テキストボックスにセット
TextRegion.Value = ActiveSheet.Cells(rngSearch.Row, rngSearch.Column + 1).Value
End If
End Sub
早速、テキストボックスで検索したい値を入力してみましょう。
このように、「品目」のテキストボックスに「さくらんぼ」と入力して、Enterキーを押すと…
この通り、下のテキストボックスに、「山形」が表示されましたね。
最後に
今回は、AfterUpdateイベントで、ふたつのテキストボックスの値を連動させる方法をご紹介しました。
今回の方法は、例えばマスタの管理画面で商品コードを入力すると、マスタ上で紐付いている商品名を別のテキストボックスに表示する、といった、マスタ管理や検索画面などで使える方法ですよ。
