【エクセルVBA】AfterUpdateイベントでテキストボックス同士を連動させてみよう


テキストボックス,afterupdate

皆様こんにちは、ノグチです。

エクセルのユーザーフォームを作って、使ってみるまでを目標に、各コントロールやプロパティ、イベントなどを連載記事でご紹介しています。

前回は、Initializeイベントで、ユーザーフォームを開いたら、ワークシートの値をユーザーフォーム上のテキストボックスに表示する方法をご紹介しました。

【エクセルVBA】Initializeイベントでワークシートの値をユーザーフォームの初期値にセットする
ユーザーフォームを作って使ってみることを目標に、連載記事でプロパティやイベントなどをご紹介しています。今回は、ユーザーフォームのInitializeイベントを使って、ワークシート上のセルの値を、ユーザーフォームを開いたときの初期値にセットする方法をご紹介しています。

今回は、テキストボックスに入力した値をワークシートから検索して、関連する値を別のテキストボックスに表示する方法をご紹介していきます!

スポンサーリンク

前回までのおさらい

前回までに作成したユーザーフォームがこちら。

オブジェクト取得,オブジェクト名

今回は、フォーム上にあるふたつのテキストボックスを連動させていきますよ。

Valueプロパティでテキストボックスの値を操作する

テキストボックスの値は、前回記事でご紹介した通り、Valueプロパティで操作できるのでしたね。

別々のテキストボックスを操作するといっても、Valueプロパティにセットされた値を使って、別のテキストボックスのValueプロパティに値をセットするだけと考えれば、難しく考えることはありません。

ただふたつのテキストボックスのValueプロパティの値を操作すればよいだけなのです。

テキストボックスに入力した値からテーブルの値を検索する

今回は、こちらのテーブルを使って、値の検索をしてみます。

テキストボックス,検索用テーブル

ユーザーフォーム上の「品目」のテキストボックスに、テーブルの「品目」にある値を入力すれば、入力値をテーブルから検索して、マッチするレコードの「生産地」の値を、ユーザーフォーム上の「生産地」のテキストボックスに表示させる、というのが今回の趣旨です。

検索用のコードを作る

早速、テキストボックスに入力した値をワークシートから検するコードを作っていきましょう。

ワークシート上から特定の値を検索する方法は、以前の記事でお伝えした方法が使えます。

【エクセルVBA】指定した範囲内から値を検索するFindメソッドとその使い方
エクセルシートに入力した値から、VBAのFindメソッドを使ってシート内を検索し、結果を返す方法をご紹介しています。Findメソッドは部分一致or全体一致、列方向or行方向など検索の条件を色々指定できて使い勝手が良いのでオススメですよ。

検索結果は、表示したいテキストボックスの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

検索に使うイベント

さて、上で作成したコードを、テキストボックスのイベントプロシージャ内に差し込んでいきましょう。

テキストボックスのイベントは、以前の記事でいくつかお伝えしました。

【エクセルVBA】テキストボックスでよく使う3つのイベントを使ってみよう
ユーザーフォームを作成して、使ってみるところまでを連載形式でご紹介しています。今回は、テキストボックスのEnter,Exit,Changeの3つのイベントをご紹介していますよ。イベントは各コントロールに数多く用意されていますが、各イベントの実行タイミングとできることを知って、便利に使ってしまいましょう!

今回はテキストボックスに入力された値を使いたいので、以前の記事の中でご紹介したイベントだと、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イベントで、ふたつのテキストボックスの値を連動させる方法をご紹介しました。

今回の方法は、例えばマスタの管理画面で商品コードを入力すると、マスタ上で紐付いている商品名を別のテキストボックスに表示する、といった、マスタ管理や検索画面などで使える方法ですよ。

それでは、最後までお読みいただきありがとうございました!

タイトルとURLをコピーしました