Excel VBA コンボボックスとリストボックスを連携させる

コントロール作成/操作

コンボボックスのリストから選択された文字列、もしくはコンボボックスのすべてのリストをリストボックスに登録する連携したツールの説明です。

さらにコンボボックスのテキスト部分に入力された文字列で、部分一致したリストに絞り込みする機能も組み込んでいるので、絞り込みされたリストを一括で登録なども可能です。

コンボボックスは1つしか文字列を表示出来ないため、リストボックスを用意して選択した文字列をさらに個別にリスト化したい場合に非常に便利なツールです。

集計したい条件を複数選択してもらう際によく利用しているツールです。

コンボボックス、コマンドボタン、リストボックスの3つのコントロールを操作する事になります。

また、ChangeイベントやInitializeイベントも使用するため、少し複雑に感じますが1つずつ順に説明したいと思います。

使い勝手も良いためユーザビリティが高く、作るのも比較的簡単なため筆者がよく利用するツールです。

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

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

ユーザーフォームに次のように、コンボボックスとコマンドボタン2つ、そしてリストボックスを設置します。

コンボボックスは「MatchEntry」プロパティを「2-fmMatchEntryNone」に指定しておきます。

そして今回リストに登録するリスト一覧です。

このリストをコンボボックスに登録して、すべてリストボックスに登録するか、もしくは絞り込みしたリストすべて登録、選択された文字列のみ登録といった機能を組み込んでいきます。

組み込まれたツールがこちらです。

ポイントはすでに登録済みの文字列は、重複して登録されない部分です。

2.ツールの仕組みについて

では、まずどのようにツールが作られているか説明します。

記述場所

記述場所はフォームモジュールと標準モジュールの2つです。

フォームモジュールにはユーザーフォームのイベントと、コントロールのイベントを記述しています。

標準モジュールには各機能のメイン処理を記述しています。

わかりやすくすべて小分けにプロシージャを作成しています。

そのため、フォームモジュールの各イベントは標準モジュールに記述されたプロシージャを呼び出すだけにしています。

ツールの構成

標準モジュールには次の5つの処理を記述しています。

関連性の強い記事のリンクが埋め込まれています。

フォームモジュールには次の4つの処理を記述しています。

3.サンプルツールのコード

サンプルツールのコードの説明です。

上記で記載した構成の順に説明します。

標準モジュール

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

特段説明するコードでもないですが、ShowメソッドでUserForm1を表示しています。

操作可能にするためにモードレスを指定しています。

完成したらこのプロシージャを実行します。

Sub FormShow()

    UserForm1.Show vbModeless
    
End Sub
コンボボックスにリストを追加する

ユーザーフォームを開いた時にコンボボックスにリストを追加する処理になります。

Sub ListAdd()

Dim i      As Long
Dim MaxRow  As Long

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

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

End With

End Sub

A列のリストを2行目から最終行までループで追加しています。

ポイントはコンボボックスのリストを一旦削除している部分です。

こちらはClearメソッドでも問題ありませんが、リスト部分のみを削除したいため「.List = Array()」で空の配列を登録する事で、削除する処理を行っています。

次のリストを絞り込むときにわかりますが、「Clear」メソッドは入力されたテキストも削除してしまうためです。

コンボボックスのリストを絞り込む

テキスト部分に文字を入力した際に、コンボボックスのリストを絞り込む処理です。

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

「TargetStr = .Value」でテキストを取得します。

こちらも同様に「.List = Array()」で一度リストを削除しています。

先程はClearメソッドでも問題ありませんでしたが、今回のプロシージャはテキスト部分を削除したくないために、Clearメソッドは使えません。

「If InStr(Cells(i, 1), TargetStr) <> 0 Then」でテキスト部分に入力された文字列が含まれるセルのみリストに登録します。

これで部分一致したリストを作成する事が出来ます。

今回は分けていますが、記述を変えれば「Sub ListAdd()」プロシージャとまとめて1つのプロシージャにする事が出来ます。

選択された1つの文字列をリストボックスに登録する

コマンドボタンの「登録」の処理です。

コンボボックスのテキスト部分に登録されている文字列をリストに登録します。

Sub ListBoxAdd()

Dim TargetStr   As String
Dim ListDic     As Object
Dim i           As Long

With UserForm1
    
    'コンボボックスのテキストを取得
    TargetStr = .Controls("ComboBox1").Value
    
    '空白で登録ボタンを押されたときの回避処理
    If TargetStr = "" Then
    
        MsgBox "入力フォームが空白です。"
    
        Exit Sub
    
    Else
        
        'リストボックスに重複登録しない判定用にDictionaryを設定
        Set ListDic = CreateObject("Scripting.Dictionary")
        
        'リストボックスのリストをループしてDictionaryに登録
        For i = 0 To .Controls("ListBox1").ListCount - 1
                       
            If Not ListDic.Exists(.Controls("ListBox1").List(i)) Then
            
                ListDic.Add .Controls("ListBox1").List(i), i
                
            End If
               
        Next i
        
        '新たに登録するコンボボックスのテキストを重複チェック
        If Not ListDic.Exists(TargetStr) Then
            
            '重複していなければ新たにリストに登録
            .Controls("ListBox1").AddItem TargetStr
            
        End If
    
    End If
    
End With

End Sub

「TargetStr = .Controls(“ComboBox1”).Value」でコンボボックスのテキスト部分を取得します。

「Set ListDic = CreateObject(“Scripting.Dictionary”)」で重複したリストを追加しないようにDictionaryを使用できるようにセットします。

「For i = 0 To .Controls(“ListBox1”).ListCount – 1」でリストボックスに登録されているリストをすべてDictionaryのKeyに格納します。

Itemはインデックスを格納していますが、これは今回は特に意味はありません。

「If Not ListDic.Exists(TargetStr) Then」でテキスト部分の文字列がすでに登録されているか判定しています。

「Not」をつけることで登録されていなければという意味になります。

「.Controls(“ListBox1”).AddItem TargetStr」でKeyに登録します。

Dictionaryの使い方については「Dictionaryの使い方」をご覧ください。

コンボボックスのリストをすべてリストボックスに登録する

コンボボックスのすべてのリストを登録する処理です。

もし絞り込みされている場合は、絞り込みされたリストをすべて登録します。

Sub ListBoxAllAdd()

Dim TargetStr   As String
Dim ListDic     As Object
Dim i           As Long

With UserForm1
    
    'リストボックスに重複登録しない判定用にDictionaryを設定
    Set ListDic = CreateObject("Scripting.Dictionary")
    
    'リストボックスのリストをループしてDictionaryに登録
    For i = 0 To .Controls("ListBox1").ListCount - 1
                   
        If Not ListDic.Exists(.Controls("ListBox1").List(i)) Then
        
            ListDic.Add .Controls("ListBox1").List(i), i
            
        End If
           
    Next i
    
    'コンボボックスに登録されているリスト分ループする
    For i = 0 To .Controls("ComboBox1").ListCount - 1
        
        'リストを順に変数に格納
        TargetStr = .Controls("ComboBox1").List(i)
        
        '重複していないか判定
        If Not ListDic.Exists(TargetStr) Then
            
            '次の文字列でも判定するために新たにDictionaryに登録
            ListDic.Add TargetStr, .Controls("ListBox1").ListCount - 1
            
            'リストボックスに登録
            .Controls("ListBox1").AddItem TargetStr
            
        End If
        
    Next i
    
End With

End Sub

基本的にはテキスト部分を登録するコードと同じですが、リストへ登録する部分がコンボボックスのリスト分ループして、すべて登録します。

「Set ListDic = CreateObject(“Scripting.Dictionary”)」でDictionaryをセットします。

「For i = 0 To .Controls(“ListBox1”).ListCount – 1」でリストボックスに登録されているすべてのリストをDictionaryのKeyに登録します。

ここからが、先ほどのコードと違う部分です。

「For i = 0 To .Controls(“ComboBox1”).ListCount – 1」でコンボボックスに登録されているリストをループします。

「TargetStr = .Controls(“ComboBox1”).List(i)」で順番にリストの文字列を変数に格納しています。

「If Not ListDic.Exists(TargetStr) Then」で格納しているリストがDictionaryに登録されていないか判定して、重複して登録しないようにします。

「ListDic.Add TargetStr, .Controls(“ListBox1”).ListCount – 1」でDictionaryに追加した文字列を追加します。

「.Controls(“ListBox1”).AddItem TargetStr」でリストボックスに追加します。

フォームモジュール

それぞれコマンドボタン「登録」のClickベント、コマンドボタン「すべて登録」のClickイベント、ユーザーフォームのInitializeイベント、コンボボックスのChangeイベントで上記標準モジュールで作成した処理を呼び出しています。 

Private Sub CommandButton1_Click()
    Call ListBoxAdd
End Sub

Private Sub CommandButton2_Click()
    Call ListBoxAllAdd
End Sub

Private Sub UserForm_Initialize()
    Call ListAdd
End Sub

Private Sub ComboBox1_Change()
    Call ListChange
End Sub

実行する

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

「Sub FormShow()」を実行すると、フォームが表示されるので、リストからリストボックスに登録するリストを選択して、「登録」ボタンを押します。

もしくはリストをすべて登録する場合は、「すべて登録」ボタンを押します。

もしテキストボックス部分に絞り込みする場合は、テキスト部分に文字を入力します。

するとコンボボックスの「Changeイベント」が実行され、リストが絞り込みされます。

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