【VBA】リストボックスに絞り込み機能を実装する方法～コピペできるサンプルコードを紹介～

 

 

プルダウンリストを使って「氏名」や「会社名」を入力している。でも、リスト項目が増えすぎて使いにくい。。。

こんな悩みを抱えながら編集作業を行っている方って意外と多いのではないでしょうか。

 

こんな感じのプルダウンリストってめちゃくちゃストレスですよね

 

 

こんな問題はVBAを使って解決できます。

 

その方法とは

プルダウンリストの代わりに『絞り込み機能がリストボックス』を採用する

です。

 

この記事では、

基礎知識から実践的なテクニックまで、VBA初心者にもイメージできるよう具体的なコード例を交えながら分かりやすく解説。

さらに、サンプルとして紹介している『別シートの名簿一覧を苗字で検索してリスト化するVBA』のダウンロード方法

をご紹介しています。

 

あなたが今処理している業務と照らし合わせながら参考にしてみてください。VBAを使って効率の悪いエクセル作業から脱却しましょう。

 

 

絞り込み機能付きリストボックスの基礎知識

 

絞り込み機能付きリストボックスはどんな処理ができるのか、どんなメリットがあるのか、をご紹介しています。

 

リストボックスの絞り込み機能とは

リストボックスの絞り込み機能は

検索ワードを入力するだけで、瞬時に必要な情報だけを表示するツール

のことです。

 

本記事では

苗字で検索した別シートの名簿情報をリスト化するVBA

をサンプル事例として紹介しています。

 

こんな感じ

 

他にも、

「役職で検索する」「所属職場で検索する」など

いろんなアレンジが可能です。

 

よくある実装パターンと活用シーン

リストボックスの絞り込み機能は、主に以下のような場面で活躍します。

• 顧客管理システムで顧客名や地域、取引状況などで情報を検索
• 在庫管理システムで商品コードや商品名などで情報を検索
• 検査管理システムで不良設備や取替予定設備などで情報を検索

 

実装パターンとしては、

テキストボックスに検索ワードを入力して、リストボックスの内容をリアルタイムで絞り込む

という仕組みが一般的です。

 

基本的な実装例がこちら

Private Sub TextBox1_Change()
Dim i As Long
Dim searchWord As String
searchWord = TextBox1.Text
ListBox1.Clear
With Worksheets("データ一覧")
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If InStr(1, .Cells(i, 1).Value, searchWord) > 0 Then
ListBox1.AddItem .Cells(i, 1).Value
End If
Next i
End With
End Sub

 

７行目から９行目のコードが絞り込み機能のメイン部分となります。

 

絞り込み機能実装のメリット

絞り込み機能付きリストボックスのメリットがこちら

• データ検索の時間が大幅に短縮
• 入力ミスの削減
• エクセル操作が苦手な方でもスムーズに編集作業を行える

 

実際のエクセル業務で『絞り込み機能付きのリストボックス』を導入して感じるメリットは

プルダウンリストよりも長く使い続けることができ、フィルターよりも簡単に目当ての情報を検索できる

です。

 

今行っているエクセル業務で

「入力作業の効率をアップさせたいなぁ。。。」「プルダウンリスト使いにくいなぁ。。。」

と感じることがあれば『絞り込み機能付きのリストボックス』の導入が絶対におすすめ。

 

 

リストボックスに絞り込み機能を実装するために必要な基礎知識

リストボックスに絞り込み機能を実装するために必要な基本知識がこちら

• 「Changeイベント」と「Clickイベント」の使い方
• For～Next（繰り返し処理）と関数IFの組み合わせ方
• 文字列操作の関数（InStr、Left、Right、Mid）の使い方

 

関連記事「引数Targetの使い方」では、クリックしてマクロを実行させる方法が紹介されています。

 

 

関連記事「For～Nextと関数IFの組み合わせる方法」では、一覧表の情報をすばやく取得する方法が紹介されています。

 

上記２つの記事では、

『絞り込み機能付きリストボックスVBA』に必要な知識が紹介されているので

併せてチェックしてみてください。

 

下記のサンプルコードは

「リストボックスを起動したときに初期化するコード」と「リストボックスにデータを読み込ませる準備コード」

が設定されています。

 

Private Sub UserForm_Initialize()
'初期化処理
Call LoadListItems
End Sub
Private Sub LoadListItems()
'リストボックスにデータを読み込む
With ListBox1
.Clear
.ColumnCount = 3
.ColumnWidths = "100;100;100"
End With
'シートからデータを読み込む処理
'ここにコードを追加
End Sub

 

『絞り込み機能付きリストボックスVBA』には、最初に上記のような設定が必要になります。

 

絞り込み機能付きリストボックスの作り方

 

 

絞り込み機能付きリストボックスを設定するための手順を説明していきます。

 

リストボックスのプロパティ設定方法

リストボックスの絞り込み機能を実装する前に、まずはプロパティの正しい設定が大切です。

 

プロパティの設定コードがこちら

With ListBox1
.ColumnCount = 3 '表示する列数
.ColumnHeads = True '列見出しを表示
.ColumnWidths = "60;100;80" '各列の幅をピクセル単位で指定
.MultiSelect = 0 '単一選択モード
.Style = fmStyleDropDownList '表示スタイルの設定
End With

 

特に気をつけたいのが、ColumnCount（列数）とColumnWidths（列幅）の設定です。

これらの値は、表示するデータの量と種類に応じて適切に設定しましょう。

 

必要なイベントプロシージャの設定

絞り込み機能を実現するために使用される３つのイベントプロシージャをご紹介します。

 

フォーム初期化時の処理

'フォーム初期化時の処理
Private Sub UserForm_Initialize()
Call InitializeListBox
End Sub

 

検索ボックスの値が変更されたときの処理

'検索ボックスの値が変更されたときの処理
Private Sub txtSearch_Change()
Call FilterListBox
End Sub

 

リストボックスの項目がクリックされたときの処理

'リストボックスの項目がクリックされたときの処理
Private Sub ListBox1_Click()
If ListBox1.ListIndex <> -1 Then
'選択された項目の処理
Dim selectedValue As String
selectedValue = ListBox1.List(ListBox1.ListIndex, 0)
'以降の処理を記述
End If
End Sub

 

これらのイベントプロシージャで絞り込み機能が付加されたリストボックスが構成されています。

 

基本的なコードの書き方

それでは、実際の絞り込み機能を実装するコードを見ていきましょう。

 

以下は、シートのデータをリストボックスに読み込み、検索ワードで絞り込む基本的な実装例です。

リストボックスの初期化処理
Private Sub InitializeListBox()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("データ一覧")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
'リストボックスをクリア
ListBox1.Clear
'全データを読み込む
With ws
For i = 2 To lastRow '1行目はヘッダーとして除外
ListBox1.AddItem .Cells(i, 1).Value '1列目
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(i, 2).Value '2列目
ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(i, 3).Value '3列目
Next i
End With
End Sub

 

'絞り込み処理
Private Sub FilterListBox()
Dim ws As Worksheet
Dim searchWord As String
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Sheets("データ一覧")
searchWord = LCase(txtSearch.Text) '検索語を小文字に統一
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
'絞り込み開始
ListBox1.Clear
If searchWord = "" Then
Call InitializeListBox '検索語が空の場合は全データ表示
Exit Sub
End If
With ws
For i = 2 To lastRow
'検索対象の文字列も小文字に統一して比較
If InStr(1, LCase(.Cells(i, 1).Value), searchWord) > 0 Or _
InStr(1, LCase(.Cells(i, 2).Value), searchWord) > 0 Then
ListBox1.AddItem .Cells(i, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(i, 2).Value
ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(i, 3).Value
End If
Next i
End With
End Sub

 

このコードのポイントはこちら

• 大文字小文字を区別しないように、比較前に文字列を小文字に統一
• 検索語が空の場合は全データを表示
• 複数列の値を対象に検索を実行
• With構文を使用してコードを効率化

 

 

苗字で名簿一覧をリスト化するVBAを作ってみた

 

実際に作成した『別シートの名簿一覧を苗字で検索してリスト化するVBA』を使って導入方法を詳しくご紹介します。

 

『別シートの名簿一覧を苗字で検索してリスト化するVBA』の概要

 

『別シートの名簿一覧を苗字で検索してリスト化するVBA』がどのような処理をするのかを解説していきます。

 

処理はこんな感じ

 

「起案者欄」をクリックすると、リストボックスが表示

 

 

リストボックスの「苗字検索」欄に検索したい苗字を入力すると別シートの名簿一覧表から検索した苗字に該当する情報だけがリストボックスに表示

 

「起案者欄」に入力したい項目をダブルクリックすると入力される

こんな処理をします。

 

別シートの名簿一覧表を編集すれば自動的にリストボックスに表示される項目が変化するので簡単に扱えます。

 

VBAコードの設定方法

 

表示されるリストボックスを作成する

 

 

UserForm1にコードを設定する

 

 

 

UserForm1に設定するコード①

Private Sub TextBox1_Change()
Dim Ash As Worksheet
Dim Csh As Worksheet
Set Ash = ThisWorkbook.Worksheets("申請書")
Set Csh = ThisWorkbook.Worksheets("所属")
lastRow = Csh.Cells(Rows.Count, 5).End(xlUp).Row
Dim A
A = Csh.Range(Csh.Cells(3, 5), Csh.Cells(Csh.Cells(Rows.Count, 5).End(xlUp).Row, 7))
Dim C
ReDim C(1 To lastRow, 1 To 3)
For i = LBound(A) To UBound(A)
If Left(A(i, 1), 1) = UserForm1.TextBox1.Value Then
cn = cn + 1
C(cn, 1) = A(i, 1)
C(cn, 2) = A(i, 2)
C(cn, 3) = A(i, 3)
ElseIf Left(A(i, 1), 2) = UserForm1.TextBox1.Value Then
cn = cn + 1
C(cn, 1) = A(i, 1)
C(cn, 2) = A(i, 2)
C(cn, 3) = A(i, 3)
ElseIf Left(A(i, 1), 3) = UserForm1.TextBox1.Value Then
cn = cn + 1
C(cn, 1) = A(i, 1)
C(cn, 2) = A(i, 2)
C(cn, 3) = A(i, 3)
Else
End If
Next i
With ListBox1
.ColumnCount = 3
.List = C
End With
rtnNo = 0
'Call SetListBox
End Sub

 

UserForm1に設定するコード②

Private Sub SetListBox()
    Dim wRow As Long
    Dim wLstRow As Long
    Dim arryID() As Variant
    Dim ListNum As Long
    Dim Ash As Worksheet
    Dim Csh As Worksheet
    Set Ash = ThisWorkbook.Worksheets("申請書")
    Set Csh = ThisWorkbook.Worksheets("所属")
    gyo = Csh.Cells(Rows.Count, 5).End(xlUp).Row
    'テキストボックスの入力値を削除したときに発生するエラーを非表示にする
    On Error Resume Next
    ListNum = UserForm1.TextBox1.Value
    On Error GoTo 0
    Me.ListBox1.Clear      '← リストボックスを初期化
    wLstRow = 0
    '配列を使って入力したID値に該当する情報をリスト化
    For i = 3 To gyo
    If ListNum = Cells(i, 2).Value Then
    ReDim arryID(2)
    arryID = Range(Cells(i, 5), Cells(i, 7))
    ListBox1.List = arryID
    Else
    End If
    Next
 End Sub

 

UserForm1に設定するコード③

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)  'リストボックスからIDをダブルクリック選択した時の処理追加
    Dim Ash As Worksheet
    Dim Csh As Worksheet
    Set Ash = ThisWorkbook.Worksheets("申請書")
    Set Csh = ThisWorkbook.Worksheets("所属")
    'Call クリア
   Ash.Cells(6, 23) = ListBox1.List(ListBox1.ListIndex, 0)
    UserForm1.ListBox1.Clear
    UserForm1.TextBox1.Value = ""
   UserForm1.Hide
End Sub

 

上記のサンプルコードをコピペしてリストボックスが動作するか試してみてください。

 

使い方

１．名簿一覧表を作成・編集する

２．リストボックスで苗字検索を行い氏名を入力する

 

 

使い方はたったこれだけ！

 

様式の変更がない限り半永久的にリストボックスを利用することが可能です。

 

最大の特徴が

別シートの名簿情報を変種するだけでリストボックスが更新される

という機能です。

 

この機能のおかげでエクセル操作が苦手な方でも感覚的に絞り込み機能付きリストボックスを利用することができます。

 

『別シートの名簿一覧を苗字で検索してリスト化するVBA』のダウンロード

下記のサイトでは『別シートの名簿一覧を苗字で検索してリスト化するVBA』が設定されたエクセルファイルをダウンロードすることができます。

 

• ダウンロードしたエクセルファイルをそのまま活用する
• VBA学習用として設定されているコードを活用する
• 設定されているコードを業務内容に合わせてアレンジして活用する

 

いろんな方法で活用可能。

 

サイトでは他にもエクセル業務の効率化に役立つVBA情報が掲載されていますので併せてチェックしてみてください。

 

 

リストボックス設定時によく発生するエラーと解決方法

 

ここからは、VBA作成時によく発生するエラーとその解決方法をご紹介していきます。

 

実行時エラーの主な原因と対処法

VBAでリストボックスの絞り込み機能を実装する際によく遭遇するエラーが

「インデックスが有効範囲にありません」と「メモリが不足しています」

の２つです。

 

これらのエラーが発生したときは慌てずに下記のような対処を試みてください。

 

「インデックスが有効範囲にありません」エラーへの対処コード

Private Sub SafeListBoxOperation()
On Error GoTo ErrorHandler
'リストボックスの選択チェック
If ListBox1.ListIndex = -1 Then
MsgBox "項目を選択してください。", vbInformation
Exit Sub
End If
'処理続行
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 381 'インデックスエラー
MsgBox "データの選択に問題が発生しました。", vbExclamation
Case Else
MsgBox "予期せぬエラーが発生しました。" & vbNewLine & _
"エラー番号：" & Err.Number & vbNewLine & _
"エラー内容：" & Err.Description, vbCritical
End Select
End Sub

 

「リストボックスで項目を選択せずにOKを実行してしまう」「指定範囲外の項目を選択してしまう」といった操作をすることで

「インデックスが有効範囲にありません」のエラーが発生することがあります。

 

これらの操作を行ってもエラーが発生しないよう予防してくれるのが上記のようなVBAです。

 

メモリ不足エラーの防止コード

Private Sub OptimizedDataLoading()
Dim dataArray() As Variant
'データを一括で配列に読み込む
With ThisWorkbook.Sheets("データ一覧")
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
dataArray = .Range("A2:C" & lastRow).Value
End With
'リストボックスの更新を一時停止
Application.ScreenUpdating = False
ListBox1.Clear
'配列からリストボックスにデータを追加
For i = 1 To UBound(dataArray)
ListBox1.AddItem dataArray(i, 1)
ListBox1.List(ListBox1.ListCount - 1, 1) = dataArray(i, 2)
ListBox1.List(ListBox1.ListCount - 1, 2) = dataArray(i, 3)
Next i
Application.ScreenUpdating = True
End Sub

 

大量のデータをFor～Next（繰り返し処理）で処理しようとすると「メモリが不足しています」のエラーが発生することがあります。

 

このエラーを防止するためには、

処理に負担がかからないコードにすること

です。

 

上記サンプルコードのように、「配列を使って範囲指定する」「処理中の更新画面を停止させる」とメモリ不足の解消に繋がります。

 

デバッグの効率的な進め方

ここからは効率的なデバックの方法をご紹介していきます。

Private Sub DebugListBoxOperations()
'デバッグモード
#If DEBUG_MODE Then
Debug.Print "--- デバッグ情報 ---"
Debug.Print "検索ワード: " & txtSearch.Text
Debug.Print "リスト件数: " & ListBox1.ListCount
'データの整合性チェック
For i = 0 To ListBox1.ListCount - 1
If IsNull(ListBox1.List(i, 0)) Then
Debug.Print "警告：Null値検出 (行: " & i + 1 & ")"
End If
Next i
#End If
End Sub

 

上記サンプルコードは

作成したVBAを実行させてエラーが発生した時点で原因を出力する

というコードです。

 

 

上記のサンプルコードを活用してデバックスキルをアップさせましょう。

 

 

パフォーマンス改善のポイント

ここからは処理効率の高いVBAの作り方をご紹介していきます。

 

サンプルコードがこちら

Private Sub PerformanceOptimizedSearch()
'アプリケーションの設定を最適化
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

'ディクショナリーを使用した高速検索
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

'検索結果を一時保存
Dim results As Collection
Set results = New Collection

'バッチ処理による効率化
Const BATCH_SIZE As Long = 1000
Dim batch() As Variant
ReDim batch(1 To BATCH_SIZE, 1 To 3)

'バッチ処理とディクショナリーを組み合わせた検索処理
'...（実装コード）

'設定を元に戻す
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub

 

上記のサンプルコードは

VBA実行に不要な処理を一時停止する

というものです。

 

大量の情報を処理するVBAや複雑な処理が求められるVBAの場合、処理完了まで長い時間を要することがあります。

 

 

パフォーマンスを意識するだけで処理速度を２倍、３倍にアップさせることが可能です。また、「メモリー不足」エラー発生の予防にも繋がるのでパフォーマンスの高いVBA作りを意識しましょう。

 

まとめ：絞り込み機能付きリストボックス導入で仕事の効率がアップ

『絞り込み機能付きリストボックス』はいかがでしたでしょうか。

 

とても便利なツールであることは間違いありませんが、

「なくても仕事はできる」「時間をかけてまで導入しなくてもいい」

と感じた方もいらっしゃると思います。

 

 

ですが、いざ『絞り込み機能付きリストボックス』を導入すると

全員が知らず知らずのうちに活用

していました。

 

なくても仕事は回る！でも、あれば100％活用する！！が『絞り込み機能付きリストボックス』です。

 

私個人的な考えとしては、

仕事の効率が少しでもアップして、半永久的に利用できるんだから絶対に導入するべき

と思っています。

 

 

マメBlogでは、VBA作成代行サービスを承っております。

 

本業が忙しくてVBAの導入に時間を費やせない。。VAB導入には興味があるけど勉強する時間がなく自分でできない。。

このような方におすすめのサービスです。

 

 

 

「今やっている業務にも設定できるの？」「費用はどれくらいかかるの？」など、いろんな疑問があるかともいます。

ご相談、お見積りの依頼は無料ですのでお気軽にお問い合わせください。