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.