Using Excel VBA to Populate a ComboBox from a Dynamic Range

This is the sample dataset.

Method 1 – Use the ActiveX Controls to Populate the ComboBox from a Dynamic Range

Select the headers of the dataset and name the range: Col_Headers in the Name Box.

Select B5:B7 and name it Category.

Name C5:12 and D5:C12 Product and Sales.
Go to the Developer tab and click Insert.
Choose ComboBox in ActiveX Controls.

Drag the cursor on the worksheet and create a box:

Create another ComboBox and choose a header for each Combobox.

Right–click the active worksheet and select View Code.

Enter this code in the Code window.

Private Sub ComboBox1_Change()
    Dim rng As Range
    Set rng = Range(Me.ComboBox1.Text)
    Me.ComboBox2.List = Application.WorksheetFunction.Transpose(rng)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("Col_Headers")
    Me.ComboBox1.List = Application.WorksheetFunction.Transpose(rng)
End Sub

In the code, ComboBox1 holds the range and ComboBox2 holds the data of each range. Col_Headers is the header range name.

Turn off Design Mode in the Developer tab.

You will see the ComboBoxes with a dynamic range. Choose an option from the Topic ComboBox and data will be displayed in the Data ComboBox.

Method 2 – Populate a ComboBox from Dynamic Range using a VBA UserForm

Press Ctrl + C to copy the dataset and press Ctrl + V in a new worksheet to paste it into A1.

Open Microsoft Visual Basic for Applications by pressing Alt + F11.
Select UserForm in the Insert tab.

Create Labels and ComboBoxes in the UserForm by dragging them from the Toolbox.

Change the Properties of each Label box. Press F4 to see the Properties Window.

Right–click any of the ComboBoxes and select View Code.

Enter this code in the Code window.

Private Sub ComboBox1_Change()
Dim wksht As Worksheet
Set wksht = ThisWorkbook.Sheets("UserForm")
Dim j, k As Integer
Me.ComboBox2.Clear
k = Application.WorksheetFunction.Match(Me.ComboBox1.Value, wksht.Range("1:1"), 0)
For j = 2 To Application.WorksheetFunction.CountA(wksht.Cells(1, k).EntireColumn)
    Me.ComboBox2.AddItem wksht.Cells(j, k).Value
Next j
End Sub
Private Sub UserForm_Activate()
Dim wksht As Worksheet
Set wksht = ThisWorkbook.Sheets("UserForm")
Dim j As Integer
Me.ComboBox1.Clear
For j = 1 To Application.CountA(wksht.Range("1:1"))
    Me.ComboBox1.AddItem wksht.Cells(1, j).Value
Next j
End Sub

In the code, Range (“1:1”) is declared as integer k, as the dataset starts in A1. Integer j=2 is declared as values, starting from Row 2 after the header. “UserForm” is the worksheet name.