Kapat

Excel Dependent List Boxes

Our template that we created it with drop-down lists before ,we prepared this time with dependent list boxes(see Create Dynamic Dependent Drop Down Lists On Userform).

Again, we have defined dynamic names for 4 columns :
1️⃣ Supplier name refers to : =OFFSET(Database!$A$2,0,0,COUNTA(Database!$A:$A)-1)
2️⃣ Category name refers to : =OFFSET(Database!$B$2,0,0,COUNTA(Database!$B:$B)-1)
3️⃣ Product name refers to : =OFFSET(Database!$C$2,0,0,COUNTA(Database!$C:$C)-1)
4️⃣ Price name refers to : =OFFSET(Database!$D$2,0,0,COUNTA(Database!$D:$D)-1)

We added the following codes to create dependent list boxes on userform into listbox_click procedures : excel dependent list boxes

Private Sub ListBox1_Click()
If ListBox1.ListIndex = -1 And IsError(Application.Match(ListBox1, Supplier, 0)) Then
        Set SD = CreateObject("Scripting.Dictionary")
        bul = ListBox1 & "*"
        For Each c In Supplier:
            If c Like bul Then SD(c) = ""
        Next c
        ListBox1.List = SD.keys
        Else
        Evn = ListBox1
        If Evn = "" Then Exit Sub
        Set d2 = CreateObject("Scripting.Dictionary")
        For i = LBound(Category) To UBound(Category)
            If Supplier(i) = Evn Then d2(Category(i)) = ""
        Next i
        tablo2 = d2.keys
        ListBox2.Clear
        ListBox3.Clear
        TextBox1 = ""
        ListBox2.List = tablo2
        ListBox2.SetFocus
        If Val(Application.Version) > 10 Then SendKeys "{f4}"
        End If
    i = Empty
    Set d2 = Nothing
End Sub

Private Sub ListBox2_Click()
If ListBox1 <> "" Then
        If ListBox2.ListIndex = -1 And IsError(Application.Match(ListBox2, Category, 0)) Then
            Set SD = CreateObject("Scripting.Dictionary")
            bul = UCase(ListBox) & "*"
            For Each c In tablo2
                If UCase(c) Like bul Then SD(c) = ""
            Next c
            ListBox2.List = SD.keys
             Else
            Set d3 = CreateObject("Scripting.Dictionary")
            If ListBox1 = "" Or ListBox2 = "" Then Exit Sub
                For i = LBound(Product) To UBound(Product)
                If Supplier(i) = ListBox1 And Category(i) = ListBox2 Then
                d3(Product(i)) = ""
                End If
            Next i
            tablo3 = d3.keys
            ListBox3.Clear
            ListBox3.List = tablo3
            ListBox3.SetFocus
            If Val(Application.Version) > 10 Then SendKeys "{f4}"
        End If
        End If
     i = Empty
     Set d3 = Nothing
End Sub

Private Sub ListBox3_Click()
 If ListBox1 <> "" And ListBox2 <> "" And ListBox3 <> "" Then
        If ListBox3.ListIndex = -1 And IsError(Application.Match(ListBox3, Product, 0)) Then
            Set SD = CreateObject("Scripting.Dictionary")
            bul = UCase(ListBox3) & "*"
            For Each c In tablo3
                If c Like bul Then SD(c) = ""
            Next c
            ListBox3.List = SD.keys
             Else
            For i = LBound(Product) To UBound(Product)
                If Supplier(i) = ListBox1 And Category(i) = ListBox2 And Product(i) = ListBox3 Then
                   TextBox1.Value = Price(i)
                              
                End If
            Next i
         End If
         End If
     i = Empty
End Sub
excel dependent listbox

📥 Download sample workbook that contains userform