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 :
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
