VBA Combobox options to be filtered by three other comboboxes

J

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I have spent a few days searching / trying to make this work, but its just not working. This is the first time I have posted, so I hope this makes sense.

I have four comboboxes in a userform. The first one looks through "Column A" and lists all individual items.

Combobox2 lists values located in "Column B", but based on the selection of Combobox1.

Likewise, for Combobox3, this pulls data from "Column C", based on Combobox1 and Combobox2.

and for Combobox4, data is pulled from "Column D", based on the three previous comboboxes.

Once the user has selected an option from all four comboboxes, I need the remaining the details from column E, F and G to display in a listbox on the same userform.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi & welcome to MrExcel.
Do you have the Dynamic Array functions like Unique & Filter?
 
Upvote 0
That's a shame, it would make it so much easier.
But never mind, give me mo
 
Upvote 0
Fluff said:
That's a shame, it would make it so much easier.
But never mind, give me mo
Click to expand...
Thank you.

I have tried to explain myself a little better with the example below.

CompanyDepartmentNamePhone NumberOrder NumberQuantityPaid?
Paper MillSalesBen
123​
PO1111
4​
Y
Paper MillSalesBen
123​
PO1114
5​
N
Paper MillSalesSimon
455​
PO2511
6​
Y
Paper MillFinancePeter
587​
PO8955
7​
Y
BlacksmithSalesJack
777​
PO8652
2​
Y
BlacksmithFinanceJack B.
766​
PO0012
1​
N
BlacksmithManufactureBeth
478​
PO5400
1​
N
BlacksmithMaterialsSue
944​
PO8545
14​
N
BlacksmithSalesJack
777​
PO9877
2​
N

ComboBox1 should list Paper Mill and Blacksmith (I have managed that)

If we assume the user selects "Paper Mill", then CB2 should only display Sales and Finance.
Assuming the user selects "Sales" in CB2, then CB3 should display "Ben" and "Simon".
Then, if we select Ben in CB3, our only option in CB4 would be "123" as that is his phone number.

Once all selections are made, the list box should have two rows of data.
1st Row: PO1111, 4, Y
2nd Row: PO1114, 5, N

I hope this helps clarify what I meant in the original post. I'm not sure how well I explained it.

Thank you :)
 
Upvote 0
Ok give this a go for the combos
VBA Code: 
Option Explicit
Dim UfDic As Object

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox4.Clear
   Me.ComboBox2.List = UfDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox4.Clear
   Me.ComboBox3.List = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub ComboBox3_Click()
   Me.ComboBox4.Clear
   Me.ComboBox4.List = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   Dim i As Long
   
   Set UfDic = CreateObject("scripting.dictionary")
   With Sheets("Master")
      Ary = .Range("A2:G" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For i = 2 To UBound(Ary)
      If Not UfDic.Exists(Ary(i, 1)) Then UfDic.Add Ary(i, 1), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1)).Exists(Ary(i, 2)) Then UfDic(Ary(i, 1)).Add Ary(i, 2), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1))(Ary(i, 2)).Exists(Ary(i, 3)) Then UfDic(Ary(i, 1))(Ary(i, 2)).Add Ary(i, 3), CreateObject("scripting.dictionary")
      UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3))(Ary(i, 4)) = Array(Ary(i, 5), Ary(i, 6))
   Next i
   Me.ComboBox1.List = UfDic.Keys
End Sub
 
Upvote 0
Fluff said:
Ok give this a go for the combos
VBA Code: 
Option Explicit
Dim UfDic As Object

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox4.Clear
   Me.ComboBox2.List = UfDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox4.Clear
   Me.ComboBox3.List = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub ComboBox3_Click()
   Me.ComboBox4.Clear
   Me.ComboBox4.List = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   Dim i As Long
  
   Set UfDic = CreateObject("scripting.dictionary")
   With Sheets("Master")
      Ary = .Range("A2:G" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For i = 2 To UBound(Ary)
      If Not UfDic.Exists(Ary(i, 1)) Then UfDic.Add Ary(i, 1), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1)).Exists(Ary(i, 2)) Then UfDic(Ary(i, 1)).Add Ary(i, 2), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1))(Ary(i, 2)).Exists(Ary(i, 3)) Then UfDic(Ary(i, 1))(Ary(i, 2)).Add Ary(i, 3), CreateObject("scripting.dictionary")
      UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3))(Ary(i, 4)) = Array(Ary(i, 5), Ary(i, 6))
   Next i
   Me.ComboBox1.List = UfDic.Keys
End Sub
Click to expand...
Works perfectly! Thank you.

How can I then feed Column E F and G into a lstbox?
 
Upvote 0
Ok, how about
VBA Code: 
Option Explicit
Dim UfDic As Object

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox4.Clear
   Me.ComboBox2.List = UfDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox4.Clear
   Me.ComboBox3.List = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub ComboBox3_Click()
   Me.ComboBox4.Clear
   Me.ComboBox4.List = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value).Keys
End Sub

Private Sub ComboBox4_Change()
   Me.ListBox1.Clear
   Me.ListBox1.List = Application.Transpose(UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value))
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant, x As Variant
   Dim i As Long
   
   Set UfDic = CreateObject("scripting.dictionary")
   With Sheets("Master")
      Ary = .Range("A2:G" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For i = 1 To UBound(Ary)
      If Not UfDic.Exists(Ary(i, 1)) Then UfDic.Add Ary(i, 1), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1)).Exists(Ary(i, 2)) Then UfDic(Ary(i, 1)).Add Ary(i, 2), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1))(Ary(i, 2)).Exists(Ary(i, 3)) Then UfDic(Ary(i, 1))(Ary(i, 2)).Add Ary(i, 3), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3)).Exists(Ary(i, 4)) Then
         UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3)).Add Ary(i, 4), Application.Transpose(Application.Index(Ary, i, Array(5, 6, 7))) 'Array(Ary(i, 5), Ary(i, 6), Ary(i, 7))
      Else
         x = UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3))(Ary(i, 4))
         ReDim Preserve x(1 To 3, 1 To UBound(x, 2) + 1)
         x(1, UBound(x, 2)) = Ary(i, 5)
         x(2, UBound(x, 2)) = Ary(i, 6)
         x(3, UBound(x, 2)) = Ary(i, 7)
         UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3))(Ary(i, 4)) = x
      End If
   Next i
   Me.ComboBox1.List = UfDic.Keys
End Sub
 
Upvote 0
Fluff said:
Ok, how about
VBA Code: 
Option Explicit
Dim UfDic As Object

Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ComboBox3.Clear
   Me.ComboBox4.Clear
   Me.ComboBox2.List = UfDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Click()
   Me.ComboBox3.Clear
   Me.ComboBox4.Clear
   Me.ComboBox3.List = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub ComboBox3_Click()
   Me.ComboBox4.Clear
   Me.ComboBox4.List = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value).Keys
End Sub

Private Sub ComboBox4_Change()
   Me.ListBox1.Clear
   Me.ListBox1.List = Application.Transpose(UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value))
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant, x As Variant
   Dim i As Long
  
   Set UfDic = CreateObject("scripting.dictionary")
   With Sheets("Master")
      Ary = .Range("A2:G" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   For i = 1 To UBound(Ary)
      If Not UfDic.Exists(Ary(i, 1)) Then UfDic.Add Ary(i, 1), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1)).Exists(Ary(i, 2)) Then UfDic(Ary(i, 1)).Add Ary(i, 2), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1))(Ary(i, 2)).Exists(Ary(i, 3)) Then UfDic(Ary(i, 1))(Ary(i, 2)).Add Ary(i, 3), CreateObject("scripting.dictionary")
      If Not UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3)).Exists(Ary(i, 4)) Then
         UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3)).Add Ary(i, 4), Application.Transpose(Application.Index(Ary, i, Array(5, 6, 7))) 'Array(Ary(i, 5), Ary(i, 6), Ary(i, 7))
      Else
         x = UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3))(Ary(i, 4))
         ReDim Preserve x(1 To 3, 1 To UBound(x, 2) + 1)
         x(1, UBound(x, 2)) = Ary(i, 5)
         x(2, UBound(x, 2)) = Ary(i, 6)
         x(3, UBound(x, 2)) = Ary(i, 7)
         UfDic(Ary(i, 1))(Ary(i, 2))(Ary(i, 3))(Ary(i, 4)) = x
      End If
   Next i
   Me.ComboBox1.List = UfDic.Keys
End Sub
Click to expand...
Thank you again. This is almost perfect. I am encountering two problems.

1)If I try to change one of the comboboxes after the listbox has been populated, I get an error with the following line of code.

Me.ListBox1.List = Application.Transpose(UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value))

2) When populating the listbox, if there is only one possible outcome, it displays the data of Column E, F and G in a single column within the listbox. However, if there are multiple results returned, they display correctly in three columns.
 
Upvote 0
JackDomino1 said:
Thank you again. This is almost perfect. I am encountering two problems.

1)If I try to change one of the comboboxes after the listbox has been populated, I get an error with the following line of code.

Me.ListBox1.List = Application.Transpose(UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value))

2) When populating the listbox, if there is only one possible outcome, it displays the data of Column E, F and G in a single column within the listbox. However, if there are multiple results returned, they display correctly in three columns.
Click to expand...
I have just fixed problem 1 by assigning the line of code to a button.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Filter data in listbox based on three conditions by selected from combobox
Replies
1
Views
165
tubrak
T
B
  • Solved
Populating combobox from table based on the header selected in another combobox (some data on different sheets)
Replies
7
Views
233
BWL388
B
A
  • Solved
using dropdown(combobox) instead of data validation to autocomplete items
2
Replies
15
Views
1K
Akuini
Akuini
P
  • Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
186
Patriot2879
P
A
  • Question
expand code to search on userform more than column by selection from combobox
Replies
1
Views
493
abdelfattah
A

Forum statistics

Threads
1,214,823
Messages
6,121,777
Members
449,049
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top