Search code examples
excelvbacombobox

Populating combobox from named ranges

I have created three dependent drop down lists using excel's validation formula. enter image description here The benefit of this is that it is easy to add more options and the dropdown list updates automatically. The structure for this is seen below (where each range is given the same name as the column header).

enter image description here

Is it possible to create the same effect using combo boxes. I can find examples of populating a combo box from hand but not automatically from named ranges

Solution

  • Here is something you can practice with.

    Create Combobox1

    Populate with a worksheet_selection Change event, the headers range is named "Headers"

    enter image description here

    The range below the headers are named according to the header names.

    Populate combobox2

    enter image description here

    Change combobox1 to populate combobox2

    The Code

    Goes into the worksheet module.

    Private Sub ComboBox1_Change()
    Dim s As String
    s = ComboBox1
    Me.ComboBox2.List = Range(s).Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ComboBox1.List = Application.WorksheetFunction.Transpose(Range("Headers"))
End Sub