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.
Multilevel dependent drop-down in User Form
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-
Visit our YouTube channel to learn step-by-step video tutorials
