VBAでユーザーフォーム上にある2つのリストボックスで、連動するリストを登録する方法をご説明します。
1つ目のリストボックスに選択されたリストをもとに、2つ目のリストボックスのリストを生成します。
リストボックスは1つ～複数のリストを選択出来ますが、複数選択された場合に連動できるように作成するには一工夫必要となるため、今回は1つの選択を前提とします。
連動させる方法と参照するリストのデータの構造についても併せてご説明します。
その他のリストボックスの操作については下記記事をご覧ください。
1.今回使用するサンプルツール
まずは今回使用するサンプルツールの説明です。
2つのリストボックスを用意して、1つ目のリストボックスのリストを選択すると、1つ目のリストに選択さた文字列に連動する2つ目のリストボックスのリストが生成されます。
イメージ用の動画です。
2.連動するリストを作るためのデータ
まずはユーザーフォームのリストボックスの操作の前に、連動するリストを作成するためのリストのデータについてご説明します。
連動するリストを作る場合、参照するデータ形式で処理するためのロジックも変わってきます。
2つの連動に限ったリストであれば次のようなデータ形式でも良いかと思います。
1つ目の条件で取得したメーカーをループして列を特定して、その列の商品名を2つ目の条件としてリストに追加するだけになります。
今回はこちらの形式を使用して、サンプルコードを作成します。
もし3つ以上の項目を想定する場合には、次のようにリストのデータ形式で用意すると対応しやすいです。
各項目が紐づいている形式でデータを作成する事で、各リストを紐づいた状態で生成できます。
3.リストボックスにリストを追加する方法
データの次はユーザーフォームに設置しているリストボックスへ、リストを追加する方法についてご説明します。
ユーザーフォームとリストボックスを用意
次のような2つのリストボックスを設置したユーザーフォームを準備します。
リストを追加する
今回は予めリストを登録しておくのは1つ目のリストボックスのみとなります。
ユーザーフォームを開く際に、予め1つ目のリストボックスへ表のメーカー部分のリストを登録します。
登録には「AddItem」メソッドを使用します。
AddItem(Item,[Index])「Item」には追加したい文字列を指定します。
「Index」は追加したい位置を指定しますが、省略可能で省略した場合は末尾に登録されます。
サンプルコード
表示と追加のコードです。
Sub FormShow()
Dim i As Long
With UserForm1
For i = 3 To 8 '列をループ
'リストボックスにリストを登録
.Controls("ListBox1").AddItem Cells(3, i)
Next i
.Show vbModeless 'ユーザーフォームを表示
End With
End Sub「For i = 3 To 8」でリストとなる列をループします。
「.Controls(“ListBox1”).AddItem Cells(3, i)」で順番にコントロールにリストを追加します。
「.Show vbModeless」でリストを追加後にユーザーフォームを表示します。
上記コードを実行すると、リストを追加したフォームが表示されます。
これではまだ表示しただけでリストをクリックしても、2つ目には何も表示されません。
4.2つ目のリストと連動させる
1つ目のリストの追加とフォームの表示が出来ましたので、次は1つ目のリストボックスを選択すると2つ目のリストボックスに連動したリストを表示させる部分になります。
連動させるには「連動の処理」部分とそれを呼び出すための「Clickイベント」の2つを作成します。
連動するコード
連動するコードです。
こちらは標準モジュールに記述しています。
Sub ListAdd()
Dim ListDic As Object
Dim i As Long
Dim n As Long
Dim KeyVal As String
'=========リスト作成=========
'Dictionaryを用意(重複しないリストを作成するため)
Set ListDic = CreateObject("Scripting.Dictionary")
With UserForm1
'2つ目のリストボックスをクリア
.Controls("ListBox2").Clear
For i = 3 To 8 '列をループ
'対象となるメーカー列を判定
If .Controls("ListBox1").List(.Controls("ListBox1").ListIndex) = Cells(3, i) Then
For n = 4 To 9
KeyVal = Cells(n, i).Value 'リストを格納
'Dictionaryに登録されているか重複判定
If Not ListDic.exists(KeyVal) Then
ListDic.Add KeyVal, "" 'Dictionaryに登録
.Controls("ListBox2").AddItem KeyVal '2つ目のリストボックスに登録
End If
Next n
Exit Sub '2つ目のリストが登録されたら抜ける
End If
Next i
End With
End Subコメントを見て頂ければ処理内容がわかるかと思いますが、簡単に説明します。
「Set ListDic = CreateObject(“Scripting.Dictionary”)」で重複したリストがある場合を想定して、重複していないリストを作成するためにDictionaryを用意します。
「.Controls(“ListBox2”).Clear」で2つ目のリストボックスのリストを一旦クリアしています。
これがないとどんどん追加されていきます。
「If .Controls(“ListBox1”).List(.Controls(“ListBox1”).ListIndex) = Cells(3, i) Then」で選択されたリストの文字列と、表のメーカーが一致した列を取得します。
「KeyVal = Cells(n, i).Value」で取得した列の商品をループしながら、変数に格納します。
「If Not ListDic.exists(KeyVal) Then」ですでにDictionaryに登録されているか判定をして、未登録であれば登録しています。
「.Controls(“ListBox2”).AddItem KeyVal 」でDictionaryに登録されてない場合は、併せてリストボックスに登録します。
Dictionaryの使い方については「Dictionaryの使い方」をご覧ください。
Clickイベントのコード
次にClickイベント部分を作成します。
Clickイベントはフォームモジュールの1つ目のリストボックスをWクリックすると自動的に作成されます。
基本的には何もイベントが作成されていなければClickイベントが自動で生成されます。
このClickイベントに先ほど作成した、2つ目のリストボックスへ連動させる処理を呼び出します。
Private Sub ListBox1_Click()
Call ListAdd
End Subこれだけです。
5.実行する
これで、表示と1つ目のリストの追加、1つ目のリストが選択されたときに2つ目のリストに連動する処理が出来ました。
あとは最初に作成した「Sub FormShow()」を実行すると、動画の様にフォームが表示されて1つ目のリストボックスのリストを選択すると連動します。
