Home>Blogs>VBA>VBA: Multilevel dependent drop-down in User Form
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.

Multilevel dependent drop-down in User Form

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-

Multilevel dependent drop-down in User Form
Multilevel dependent drop-down in User Form

 

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step by step video tutorial:

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com

Leave a Reply