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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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

M
  • Question Question
sum or subtraction based on selected item from combobox on userform
Replies
3
Views
202
Mussa
M
J
  • Question Question
Filter ListBox on a UserForm using a ComboBox
Replies
1
Views
296
DanteAmor
DanteAmor
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
190
krunal123
K
A
  • Solved
doesn't show data in listbox on form without any error in this code
Replies
6
Views
115
Abdo
A
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
194
Candyman8019
C

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

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