Create dependent combo boxes in Excel user form with VBA

We can create dependent combo boxes in Excel user form with VBA quickly and then use the displayed to perform more actions like for example checking available quantities of items in our inventory. Watch the training video below before delving into the VBA macro code solution:

Watch the video on YouTube.

The complete VBA code:

Private Sub ComboBox1_Change()
Me.ComboBox2 = “”
Select Case Me.ComboBox1
Case “Department”
Me.ComboBox2.RowSource = “Department”

Case “Admin”
Me.ComboBox2.RowSource = “Admin”

Case “R_D”
Me.ComboBox2.RowSource = “R_D”

Case “Marketing”
Me.ComboBox2.RowSource = “Marketing”

Case “Sales”
Me.ComboBox2.RowSource = “Sales”
End Select

End Sub

Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Dim lastrow As Long
Dim lastcolumn As Long
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
With Worksheets(“sheet1”)
    For I = 1 To lastcolumn
With .Columns(I)
lastrow = Sheet1.Cells(Rows.Count, I).End(xlUp).Row
With Range(Cells(1, I), Cells(lastrow, I))
Range(Cells(1, I), Cells(lastrow, I)).Select
Selection.CreateNames Top:=True
End With
End With
Next I
End With
Me.ComboBox1.RowSource = “Department”
End Sub

Download a sample file for practice: