Matching ComboBox Controls

Excel VBA: Dependent Lists for Combobox Controls

Got any Excel/VBA Questions? Free Excel Help

Matching ComboBoxes

We can use Excel VBA to have one ComboBox Control dependent on the item chosen in another. It makes use of dynamic named ranges and the lists laid out on a Worksheet (can be hidden), with appropriate headings and each list named the same as the items in the first ComboBox. Then we can use code like shown below:

Private Sub ComboBox1_Change()

Dim strRange As String

    If ComboBox1.ListIndex > -1 Then

       strRange = ComboBox1

       Label2.Caption = strRange

       strRange = Replace(strRange, " ", "_")

            With ComboBox2

                .RowSource = vbNullString

                .RowSource = strRange

                .ListIndex = 0

            End With

    Else

       Label2.Caption = "Associated Items"

    End If

End Sub

Note the use of Replace to replace any space characters with the underscore. This is needed in some cases as range names cannot contain spaces

Download the Workbook Example