【Excel VBA】UserFormのListBoxを文字入力でリアルタイム検索する方法｜インクリメンタルサーチ

みなさんこんにちは。

Excel VBAでUserFormを作成していると、ListBoxに表示するデータ件数が多くなり、目的のデータを探すのに時間がかかった経験をしたことがある方も多いかと思います。

このような場合は、TextBoxへ文字を入力するたびにListBoxの表示内容を絞り込むようにすると、目的のデータを探しやすくなります。

検索ボタンを押さなくても、1文字入力するたびに候補を更新できるため、実際の業務で使用するUserFormでも便利な機能となります。

今回は、UserFormのTextBoxへ文字を入力するたびにListBoxをリアルタイムで絞り込む、インクリメンタルサーチの作成方法についてご紹介したいと思います。

この記事はこんな人におすすめ

• UserFormのListBoxから目的のデータを検索したい人
• TextBoxへの入力と同時に候補を絞り込みたい人
• ListBoxを部分一致で検索したい人
• 検索文字を削除したときに全件表示へ戻したい人
• UserFormをより使いやすい検索画面にしたい人

今回作成するもの

今回は、Sheet1に登録した顧客名をUserFormのListBoxへ表示し、TextBoxへ文字を入力すると該当するデータだけを表示する画面を作成します。

例えば「田」と入力した場合は、「田中商事」「田村工業」「山田商店」など、「田」を含むデータだけがListBoxへ表示されるようにします。

• TextBox1：検索したい文字を入力する
• ListBox1：検索結果をリアルタイムで表示する
• Sheet1：検索元となる顧客名を保存する

検索元となるデータを準備する

まずは、Sheet1のA列へ検索元となるデータを用意します。

A1セルを「顧客名」とし、A2セル以降へ次のようなデータを入力してください。

• 田中商事
• 田村工業
• 山田商店
• 佐藤商事
• 鈴木株式会社
• 高橋物産
• 伊藤システム

今回はA列だけを使用しますが、実際の業務では社員名、商品名、学校名、施設名などの一覧にも応用できます。

UserForm表示時に元データを読み込む

元データを配列へ保存する

検索するたびにSheet1へアクセスすると、データ件数が増えたときに処理が重くなる可能性があります。

そこで今回は、UserFormを表示したタイミングでSheet1のデータを一度だけ配列へ読み込み、その配列を使って検索するようにします。

UserFormのコード画面の先頭へ、次の変数を追加してください。

Option Explicit

'========================================
' 検索元となる顧客名を保持する配列
' UserFormを表示している間、このデータを使って検索する
'========================================
Private sourceData As Variant

UserForm_Initializeでデータを読み込む

続いて、UserFormを表示したときにSheet1から元データを取得し、ListBoxへ全件表示します。

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim lastRow As Long

    '========================================
    ' 検索元となるワークシートを取得する
    '========================================
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    '========================================
    ' A列の最終行を取得する
    '========================================
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    '========================================
    ' 検索対象となるデータが存在するか確認する
    '========================================
    If lastRow < 2 Then

        MsgBox "検索対象のデータがありません。", vbExclamation
        Exit Sub

    End If

    '========================================
    ' A2から最終行までの顧客名を配列へ一括取得する
    '========================================
    sourceData = ws.Range("A2:A" & lastRow).Value

    '========================================
    ' UserForm表示時は全件をListBoxへ表示する
    '========================================
    Call DisplayAllData

End Sub

これで、UserFormを開いたときにSheet1の顧客名がすべてListBoxへ表示されます。

TextBoxへ入力するたびにListBoxを絞り込む

TextBox_Changeイベントを使用する

今回のポイントとなるのが、TextBox_Changeイベントです。

ChangeイベントはTextBoxの内容が変更されるたびに実行されるため、1文字入力するたびに検索処理を実行できます。

Private Sub TextBox1_Change()

    '========================================
    ' TextBoxの内容が変更されるたびに
    ' ListBoxの表示内容を絞り込む
    '========================================
    Call FilterListBox(Me.TextBox1.Value)

End Sub

検索ボタンを用意しなくても、TextBoxへ文字を入力した瞬間にListBoxの内容が更新されるようになります。

部分一致でListBoxを絞り込む

次に、TextBoxへ入力された検索文字を含むデータだけをListBoxへ追加します。

部分一致の判定にはInStr関数を使用します。

Private Sub FilterListBox(ByVal searchWord As String)

    Dim i As Long

    '========================================
    ' 前回の検索結果を削除する
    '========================================
    Me.ListBox1.Clear

    '========================================
    ' 検索文字が空欄の場合は全件表示へ戻す
    '========================================
    If searchWord = "" Then

        Call DisplayAllData
        Exit Sub

    End If

    '========================================
    ' 元データを先頭から順番に確認する
    '========================================
    For i = 1 To UBound(sourceData, 1)

        '========================================
        ' 検索文字が含まれているか部分一致で判定する
        ' vbTextCompareを指定して大文字・小文字を区別しない
        '========================================
        If InStr(1, CStr(sourceData(i, 1)), _
                    searchWord, vbTextCompare) > 0 Then

            '条件に一致したデータだけListBoxへ追加する
            Me.ListBox1.AddItem sourceData(i, 1)

        End If

    Next i

End Sub

InStrの戻り値が0より大きい場合は、元データの中に検索文字が含まれていることを意味します。

例えばTextBoxへ「田」と入力すると、「田中商事」「田村工業」「山田商店」のように、「田」を含むデータだけが表示されます。

検索文字を削除したら全件表示へ戻す

インクリメンタルサーチでは、検索文字をすべて削除したときに元の一覧へ戻るようにしておくと使いやすくなります。

今回はsearchWord = ""の場合に、DisplayAllDataを呼び出して全件表示へ戻しています。

Private Sub DisplayAllData()

    Dim i As Long

    '========================================
    ' ListBoxの表示内容を初期化する
    '========================================
    Me.ListBox1.Clear

    '========================================
    ' 元データをすべてListBoxへ追加する
    '========================================
    For i = 1 To UBound(sourceData, 1)

        Me.ListBox1.AddItem sourceData(i, 1)

    Next i

End Sub

TextBoxへ「田」と入力したあと、入力した文字を削除するとListBoxへすべての顧客名が再表示されます。

大量データを扱う場合の注意点

今回の方法は、数百件から数千件程度の一覧をUserForm上で検索する場合にも使いやすい方法です。

ただし、データ件数が非常に多い場合は、1文字入力するたびにすべてのデータをループするため、環境によっては入力時に少し重く感じる場合があります。

そのような場合は、今回のようにSheet1を毎回読み直さず、元データを最初に配列へ読み込んでおくことが大切となります。

さらに件数が多い場合は、検索開始文字数を2文字以上にする、検索ボタン方式へ変更するなど、利用するデータ量に合わせた方法を検討してみてください。

今回使用したコードの流れ

UserForm表示 → Sheet1から元データを配列へ読み込み → ListBoxへ全件表示 → TextBoxへ入力 → Changeイベント → InStrで部分一致 → 一致したデータだけListBoxへ再表示

検索処理そのものはシンプルですが、元データを配列へ保持しておくことで、実際の業務でも使いやすい形になります。

過去の記事のご紹介

ListBoxについては、複数の項目を選択して、選択したデータだけをシートへ転記する方法についてもご紹介しています。

今回の検索機能と組み合わせることで、「検索して候補を絞る → 必要なデータを選択する」といったUserFormも作成できるかと思います。

【Excel VBA】UserFormのListBoxを複数選択して選択したデータをシートへ転記する方法

最後に

いかがでしたでしょうか。

今回は、Excel VBAのUserFormでTextBoxへ文字を入力するたびにListBoxの候補をリアルタイムで絞り込む方法についてご紹介しました。

TextBoxのChangeイベントとInStrによる部分一致を組み合わせることで、検索ボタンを押さなくても候補を絞り込めます。

また、検索文字が空欄になった場合は全件表示へ戻すことで、利用者にも分かりやすい検索画面になるかと思います。

ListBoxのデータ件数が多く、目的の項目を探しにくい場合に少しでも参考になれば幸いです。

開発依頼について

ココナラでWebスクレイピング開発サービスを出品しております。

自分で開発をしようと思ったけど、VBAでのネット記事が少なく困っている方も多いかと思います。

そんな方はいつでもお気軽にご相談ください。

また、本ブログからご依頼いただいた方については割引特典がございますので、

ご不明点と合わせてメッセージをいただけると幸いです。

Webスクレイピング以外にもWebアプリの開発サービスも出品しております。

こちらについても、本ブログからご依頼いただいた方については割引特典がございますので、

お気軽にご相談ください。

ご興味がある方はこちら！！

@KP16Wt9UOB97870