VBA Combobox options to be filtered by three other comboboxes

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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Do you have the Dynamic Array functions like Unique & Filter?
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Do you have the Dynamic Array functions like Unique & Filter?
Hi, thank-you for the welcome.

No, I do not have the Dynamic Array functions
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
That's a shame, it would make it so much easier.
But never mind, give me mo
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That's a shame, it would make it so much easier.
But never mind, give me mo
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 :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
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
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Works perfectly! Thank you.

How can I then feed Column E F and G into a lstbox?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
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
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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
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.
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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.
I have just fixed problem 1 by assigning the line of code to a button.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

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
Top