Excel VBA コンボボックスのChangeイベント リストを部分一致で絞り込み

コントロール作成/操作

コンボボックスのテキストを変更すると、入力されているテキストを含んだ文字列のみのリストに変更する方法です。

コンボボックスに登録されているリストの数が少なければ、リストを選択時に探すことが簡単で問題ないのですが、リストの数が多くなると探すのが非常に大変になります。

そこでコンボボックスのChangeイベントを使って、テキストに文字列が入力される、もしくは変更されると文字列に部分一致したリストを作成することで、ユーザーがリストの選択を簡単に行えます。

実際に絞り込みをするサンプルツールを使って説明したいと思います。

コンボボックスの追加方法やイベントについては、下記記事をご覧ください。

1.今回使用するサンプルツール

まずは今回使用するサンプルツールについてご説明します。

コンボボックスを用意して、テキスト入力部分に文字列を入れると、テキスト入力部分に入力された文字列に部分一致したリストに更新されます。

今回登録するリストです。

上記リストが登録されたコンボボックスに「A」「B」「C」、「1001」と入力して、リストが絞り込まれます。

今回はこちらのツールを作成しつつ、Changeイベントについて説明します。

2.Changeイベントとは

ChangeイベントはコンボボックスのValueプロパティが、変更されたら実行されるイベントです。

大抵のコントロールには備わっている基本的なイベントで、もっとも使用頻度の高いイベントだと思います。

Changeイベントの作成方法

Changeイベントは、イベントを組み込みたいコンボボックスが設置されている、ユーザーフォームのフォームモジュールに作成します。

作成するにはユーザーフォーム上のコンボボックスをWクリック、もしくは右クリックで「コードの表示」を選択します。

ユーザーフォームをWクリック、もしくは右クリックで「コードの表示」でもフォームモジュールを表示して作成可能ですが、初めからテキストボックスから表示した方が楽です。

対象コンボボックスからフォームモジュールを開くと、基本的にChangeイベントが自動的に生成されます。

イベントが生成されたら、あとはイベントプロシージャ内に実行したいコードを記述します。

3.サンプルツール

Changeイベントの作成方法の説明はここまでにして、動画紹介したサンプルツールの作り方です。

今回のツールは次のように構成を分けてます。

  • ユーザーフォームを表示する(標準モジュール)
  • リストを追加する(標準モジュール)
  • リストを変更する(標準モジュール)
  • ユーザーフォームのInitializeイベントで表示時のリスト追加(フォームモジュール)
  • コンボボックスのChangeイベントでリストを変更する(フォームモジュール)

※リストの追加と変更はコードの記述方法次第で、1つにまとめる事も出来ます。

ユーザーフォームとコントロールの準備

まずはユーザーフォームとコンボボックスを作ります。

フォームモジュールを追加して、コンボボックスを1つ設置します。

コンボボックスの追加は「コンボボックスのイベント一覧と使い方」をご覧ください。

ここでのポイントとして、コンボボックスのプロパティの「MatchEntry」を「2-fmMatchEntryNone」に指定します。

「MatchEntry」は入力されたテキストの検索方法を指定するプロパティです。

これをしておかないと今回のツールでは「A」と入力すると「A1001」と勝手に変換されてしまい、リストを絞り込めません。

標準モジュール

フォームを作成したあとはユーザーフォームを表示するコードと、リストを追加するコードと、リストを変更するコードを標準モジュールに記述します。

フォームモジュールに直接記述しても構いませんが、今回は追加と変更を分けるため、標準モジュールに作成して呼び出すだけにしたいと思います。

ユーザーフォームを表示する

作成したUserForm1をモードレスで表示します。

Sub FormShow()

    UserForm1.Show vbModeless
    
End Sub
リストを追加するコード

今回は文頭で紹介したリストを追加します。

リストの追加や削除については、「コンボボックスのリストの追加と削除する方法」をご覧ください。

ここでポイントがあり、リストを削除する際に「Clear」メソッドを使うと入力されたテキスト自体も削除されます。

それを避けるために「.List = Array()」でリストに空の配列を登録する事で、リストのみ削除する事が出来ます。

Sub ListAdd()

Dim i      As Long
Dim MaxRow  As Long

'リストの最終行を取得
MaxRow = Cells(Rows.Count, 1).End(xlUp).Row

With UserForm1.Controls("ComboBox1")
    
    '一度リストを削除する
    .List = Array()
    
    For i = 2 To MaxRow
    
        .AddItem Cells(i, 1).Value
    
    Next i

End With

End Sub
リストを変更するコード

テキスト入力部分が変更されたときに、入力されたテキストと部分一致するリストを作成する部分です。

Sub ListChange()

Dim i           As Long
Dim MaxRow      As Long
Dim TargetStr   As String

'リストの最終行を取得
MaxRow = Cells(Rows.Count, 1).End(xlUp).Row

With UserForm1.Controls("ComboBox1")

    TargetStr = .Value 'テキストを取得
    
    '一度リストを削除する
    .List = Array()
        
    For i = 2 To MaxRow
    
        '取得した文字列が含まれる場合のみリストに登録
        If InStr(Cells(i, 1), TargetStr) <> 0 Then
    
            .AddItem Cells(i, 1).Value
            
        End If
    
    Next i

End With

End Sub

「If InStr(Cells(i, 1), TargetStr) <> 0 Then」でテキストに入力された文字列を含むか判定しています。

フォームモジュール

次はフォームモジュールに記述するコードです。

ユーザーフォームを表示されるときに実行されるリストを追加する「Initializeイベント」と、コンボボックスのテキストが変更されたときに実行される「Changeイベント」を記述します。

ユーザーフォームのInitializeイベントについては「ユーザーフォームのInitializeイベント 表示される時に実行」をご覧ください。

コード自体はすでに標準モジュールに記述していますので、単純に呼び出すだけになります。

Option Explicit

Private Sub UserForm_Initialize()
    Call ListAdd
End Sub

Private Sub ComboBox1_Change()
    Call ListChange
End Sub

「Private Sub UserForm_Initialize()」でフォームが表示されるときに、「ListAdd」プロシージャを呼び出します。

「Private Sub ComboBox1_Change()」でテキストが変更されたときに「ListChange」プロシージャを呼び出します。

実行する

標準モジュールとフォームモジュールのイベントが出来ましたので、実行します。

「Sub FormShow()」を実行すると、フォームが表示されるのであとはテキストボックス部分に絞り込みたい文字を入力します。

あとは「Changeイベント」が実行されるので、部分一致で絞り込みされたリストが表示されます。

スポンサーリンク
タイトルとURLをコピーしました