ユーザーフォームのComboBox、表示される選択肢が多すぎで困ることがありませんか？
選択肢が100個あってスクロールが大変
似たような名前ばかりで探すのが大変
選択肢の絞り込みができれば、こんな問題が一気に解決します。
というわけで今回は、ComboBoxに表示される選択肢の絞り込みをするコードを紹介します。
実際の動きとしてはこんな感じになります。
一文字入力すると、入力した文字が入っている県だけに絞り込むことができました。
それでは、実際のコードを見ながら解説していきます！今回は３段階に分けて作っていく過程もご紹介しています。完成版だけコピペしたい場合は目次からStep3にとんでください(^^)/
Step1：ComboBoxに選択肢を追加する方法
まず、ComboBoxに選択肢を追加する方法です。こちらはComboBoxを作成した時みなさん作られるコードだと思うのでサクッとご紹介。
ユーザーフォームが表示された時点でComboBoxに選択肢が追加されている必要があるのでInitializeのタイミングで下記のコードを実行します。
Private Sub UserForm_Initialize()
Dim LastRow As Long, i As Long
'選択肢のリストの最終行を取得
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'選択肢のリストの範囲でForループを回して1個ずつ選択肢を追加
For i = 2 To LastRow
ComboBox1.AddItem Cells(i, 2).Value
Next i
End Sub
上の画像のように２列目に都道府県リストを作っていて、2行目から48行目までが追加したい選択肢となっています。
For文を使って2行目から48行目までのセルの文字（Cells(i, 2).Value）をComboBox1.AddItemというものを使ってComboBoxの選択肢に追加しているというコードになります。
これだと全部の県が選択肢に追加されます。
では、入力した文字列に該当する県だけに絞り込むにはどうしたらいいでしょうか？
Step２：選択肢の絞り込みをする方法
ComboBox1.AddItem で選択肢を追加しているので、このコードが実行される条件を追加してあげればOKです。
条件としては、「入力した文字列が入っている県であれば選択肢に追加」となります。
Private Sub UserForm_Initialize()
Dim LastRow As Long, i As Long
Dim tmp As String
'ConboBoxに入力した文字を取得
tmp = ComboBox1.Value
'選択肢のリストの最終行を取得
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'選択肢のリストの範囲でForループを回して1個ずつ選択肢を追加
For i = 2 To LastRow
'入力した文字が入っている県だけを追加するIf文
If InStr(Cells(i, 2), tmp) > 0 Then
ComboBox1.AddItem Cells(i, 2).Value
End If
Next i
End Sub
InStr(Cells(i, 2), tmp) > 0 という条件を使って、入力した文字(tmp）が Cells(i, 2) の中に含まれているかを確認しています。
ちなみにInStrは、何文字目にその文字が入っているかを調べるためのものです。
例えば「あいうえお」という文章の中に「う」が何文字目に入っているかを調べるときは
tmp = InStr(“あいうえお”,”う”)
とすると、tmpという変数の中には3という数字が入ります。「う」が3文字目にあるからですね。
これを応用して、「あいうえお」の中に「う」という文字が入っているかどうかを調べるIf文はこのようになります。
If InStr( “あいうえお”,”う” ) > 0 then
「う」という文字が入っていれば、何文字目かという数字（今回であれば3）が返ってくるので、0より大きくなります。
逆に文字が入っていない場合は0が返ってくるので、このIf文に当てはまらなくなるという仕組みです。
それをふまえてもう一度コードを見てみます。
If InStr(Cells(i, 2), tmp) > 0 then
という条件なので、入力した文字(tmp）が Cells(i, 2) の中に含まれているかを確認している、というわけです。
これで「入力した文字列が入っている県であればComboBoxの選択肢に追加」というコードが書けました。
次は、このコードをどのタイミングで実行するかの検討です。
Step３：選択肢を絞り込むタイミングを検討
さて、さきほど一時的にIntializeのタイミングで絞り込みのコードを作ってみましたが、実際はInitializeでは全部の県を追加してほしいのでIf文を追加したコードは別のところに書かなきゃいけません。
コードの実行タイミングはChange、Click、などいろいろありますが、今回は
DropButtonClick
を使ってみます。
これは、ComboBoxの選択肢リストが表示/非表示されるタイミングで実行されます。
赤丸のところを押した時、ということですね。
（※実際には、それ以外にもリストが非表示になるタイミングでも実行されます。どういう状況かというと、選択肢の中からどれかの県の名前を選んだ時にリストが非表示になり、このタイミングでも実行されるということです）
コードは先ほどとほとんど同じです。
・既に追加されている選択肢をクリアする
・入力した文字もクリアされてしまうので、tmpに保存しておいた文字をComboBox1の値に設定する
というコードが追加されているだけです。
Private Sub ComboBox1_DropButtonClick()
Dim LastRow As Long, i As Long
Dim tmp As String
'ComboBoxに入力した文字を取得
tmp = ComboBox1.Value
'選択肢のリストの最終行を取得
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'既に追加されている選択肢を削除
ComboBox1.Clear
'選択肢のリストの範囲でForループを回して1個ずつ選択肢を追加
For i = 2 To LastRow
'入力した文字が入っている県だけを追加するIf文
If InStr(Cells(i, 2), tmp) > 0 Then
ComboBox1.AddItem Cells(i, 2).Value
End If
Next i
'tmpに保存しておいた文字をComboBox1の値に設定
ComboBox1.Value = tmp
End Subこれで、冒頭の動画のように入力した文字の県だけに絞り込むことができました。
Initializeと DropButtonClickのコードをまとめてコピーしたい場合は下のコードをコピーしてください。
Private Sub UserForm_Initialize()
Dim LastRow As Long, i As Long
'選択肢のリストの最終行を取得
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'選択肢のリストの範囲でForループを回して1個ずつ選択肢を追加
For i = 2 To LastRow
ComboBox1.AddItem Cells(i, 2).Value
Next i
End Sub
Private Sub ComboBox1_DropButtonClick()
Dim LastRow As Long, i As Long
Dim tmp As String
'ConboBoxに入力した文字を取得
tmp = ComboBox1.Value
'選択肢のリストの最終行を取得
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
'既に追加されている選択肢を削除
ComboBox1.Clear
'選択肢のリストの範囲でForループを回して1個ずつ選択肢を追加
For i = 2 To LastRow
'入力した文字が入っている県だけを追加するIf文
If InStr(Cells(i, 2), tmp) > 0 Then
ComboBox1.AddItem Cells(i, 2).Value
End If
Next i
'tmpに保存しておいた文字をComboBox1の値に設定
ComboBox1.Value = tmp
End Sub今回はConboBoxの選択肢の絞り込みをする方法を紹介しました。
使えると結構便利なのでぜひ取り入れてみてください！
コードの解説を動画でもしています。１行ずつ実行しながら解説しているので、文字で見るよりわかりやすくなっていると思います。
ぜひ見てみてください。
今回は以上です！この記事がどなたかの参考になれば嬉しいです。
お問い合わせ、お仕事のご依頼などがありましたら下記のお問い合わせフォームからご連絡お願いします。
お問い合わせフォーム
自分で使うVBAの書き方で詰まっている方のお手伝いであれば5,000円ほどから、VBAを一から作成して納品であれば3万円ほどから承ります。
コメント