【Excel VBA】UserFormのComboBoxを連動させる方法｜1つ目の選択内容で2つ目の候補を絞り込む

みなさんこんにちは。

Excel VBAでUserFormを作成していると、1つ目のComboBoxで選択した内容によって、2つ目のComboBoxに表示する候補を変更したい場面があるかと思います。

例えば、1つ目のComboBoxで「果物」を選択した場合は、2つ目のComboBoxに「りんご」「みかん」「バナナ」を表示し、1つ目のComboBoxで「飲み物」を選択した場合は、「コーヒー」「紅茶」「水」を表示するといった使い方です。

今回は、UserFormに配置した2つのComboBoxを連動させ、1つ目の選択内容に応じて2つ目の候補を絞り込む方法についてご紹介したいと思います。

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

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

Excel VBAでUserFormを作成している人
ComboBoxの基本的な使い方を知りたい人
2つのComboBoxを連動させたい人
1つ目の選択内容によって2つ目の候補を絞り込みたい人

といったところでしょうか。

UserFormに関する過去の記事

UserFormの表示方法については、過去の記事でもご紹介しておりますのでそちらもどうぞ。

【VBA×初学者向け】ユーザーフォームを表示する方法

今回やりたいこと

今回は、UserFormに2つのComboBoxを配置します。

ComboBox1：分類を選択
ComboBox2：ComboBox1で選択した分類に該当する候補を表示

例えばComboBox1で「果物」を選択した場合は、ComboBox2に「りんご」「みかん」「バナナ」だけを表示するようにします。

マスタデータを作成する

まずは、ComboBoxに表示する候補をExcelシートに作成します。

今回は「マスタ」というシートを作成して、A列に分類、B列に候補を入力します。

分類	候補
果物	りんご
果物	みかん
果物	バナナ
飲み物	コーヒー
飲み物	紅茶
飲み物	水

UserForm起動時にComboBox1へ分類を読み込む

まずはUserFormを開いた際に、ComboBox1へ「果物」「飲み物」を表示します。

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim dict As Object
    Dim categoryName As String

    Set ws = ThisWorkbook.Worksheets("マスタ")
    Set dict = CreateObject("Scripting.Dictionary")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow

        categoryName = CStr(ws.Cells(i, "A").Value)

        If categoryName <> "" Then

            If Not dict.Exists(categoryName) Then
                dict.Add categoryName, True
                Me.ComboBox1.AddItem categoryName
            End If

        End If

    Next i

End Sub

マスタシートのA列を上から確認して、まだComboBox1へ追加されていない分類だけを追加しています。

Scripting.Dictionaryを使用することで、「果物」が3行登録されていてもComboBox1には1回だけ表示されるようにしています。

ComboBox1の選択内容でComboBox2を絞り込む

次に、ComboBox1の選択内容が変更された時にComboBox2へ候補を追加します。

Private Sub ComboBox1_Change()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("マスタ")

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Me.ComboBox2.Clear

    For i = 2 To lastRow

        If CStr(ws.Cells(i, "A").Value) = CStr(Me.ComboBox1.Value) Then

            If ws.Cells(i, "B").Value <> "" Then
                Me.ComboBox2.AddItem ws.Cells(i, "B").Value
            End If

        End If

    Next i

End Sub

ComboBox1の内容が変更されるたびに、最初にComboBox2の内容をクリアします。