While creating the data entry form in VBA, we need to put several validations. One of them is dependent drop-down. In this article you will learn how to create multilevel dependent drop-down in VBA user form.
To create the dependent drop-down list, we have to create the data in Excel sheet as given in below image.
Below is the VBA code for which we have to put on User form activation, and change even of Combobox-1, Combobox-2 and Combobox-3.
Private Sub ComboBox1_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim i As Integer
Me.ComboBox2.Clear
For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
If sh.Range("A" & i).Value = "Sub Category" Then
If sh.Range("C" & i).Value = Me.ComboBox1.Value Then
Me.ComboBox2.AddItem sh.Range("B" & i).Value
End If
End If
Next i
End Sub
Private Sub ComboBox2_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim i As Integer
Me.ComboBox3.Clear
For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
If sh.Range("A" & i).Value = "Item" Then
If sh.Range("C" & i).Value = Me.ComboBox2.Value Then
Me.ComboBox3.AddItem sh.Range("B" & i).Value
End If
End If
Next i
End Sub
Private Sub ComboBox3_Change()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim i As Integer
Me.ComboBox4.Clear
For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
If sh.Range("A" & i).Value = "Sub Item" Then
If sh.Range("C" & i).Value = Me.ComboBox3.Value Then
Me.ComboBox4.AddItem sh.Range("B" & i).Value
End If
End If
Next i
End Sub
Private Sub UserForm_Activate()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim i As Integer
Me.ComboBox1.Clear
For i = 2 To sh.Range("A" & Application.Rows.Count).End(xlUp).Row
If sh.Range("A" & i).Value = "Category" Then
Me.ComboBox1.AddItem sh.Range("B" & i).Value
End If
Next I
End Sub
Below is the user form snapshot-
Click here to download this Excel workbook.
