Populating combobox from named ranges

Question

I have created three dependent drop down lists using excel's validation formula. 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).

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

Answer 1

Here is something you can practice with.

Create Combobox1

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

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

Populate combobox2

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