Dependent Drop-Down in VBA User Form
VBA

VBA: Multilevel dependent drop-down in User Form

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.

 

Data for dependent drop-down
Data for dependent drop-down

 

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-

Userform
User form

 

Click here to download this Excel workbook.

 

Watch the step by step video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply

Your email address will not be published. Required fields are marked *