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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Ok, replace Combo4 & the initialize with
VBA Code:
Private Sub ComboBox4_Click()
   Me.ListBox1.Clear
   If UBound(UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value), 2) > 1 Then
      Me.ListBox1.List = Application.Transpose(UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value))
   Else
      With Me.ListBox1
         .AddItem UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value)(1, 1)
         .List(0, 1) = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value)(2, 1)
         .List(0, 2) = UfDic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)(Me.ComboBox4.Value)(3, 1)
      End With
   End If
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(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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
The above will also cure the problem for part 1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

JackDomino1

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

ADVERTISEMENT

You're welcome & thanks for the feedback
Furthering from yesterday. Is it possible to filter worksheet "Master" based in on the values in the 4 comboboxes. I plan to then copy the filtered worksheet into a report. I think I can manage the copying into a report function, but Im struggling with the filter based on the CB values.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub CommandButton1_Click()
   Dim i As Long
   
   With Sheets("Master")
      For i = 1 To 4
         If Me.Controls("ComboBox" & i).Value <> "" Then
            .Range("A1:G1").AutoFilter i, Me.Controls("ComboBox" & i).Value
         End If
      Next i
   End With
End Sub
 

JackDomino1

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

ADVERTISEMENT

How about
VBA Code:
Private Sub CommandButton1_Click()
   Dim i As Long
  
   With Sheets("Master")
      For i = 1 To 4
         If Me.Controls("ComboBox" & i).Value <> "" Then
            .Range("A1:G1").AutoFilter i, Me.Controls("ComboBox" & i).Value
         End If
      Next i
   End With
End Sub
Brilliant. Thank you again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
My pleasure & thanks for the feedback
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
My pleasure & thanks for the feedback
I wonder if you might be able to help with this? Its for the same project. I posted another thread, but have had no joy.

I'm looking to have a button the userform. Once pressed, allow the user to select a CSV file and open it. The code then needs to take an average of a specified range, for example B2:B14 and report the result in a cell in "sheet2". The CSV can then close.

Note, the file directory of the CSV will always be different, so the user has to be able to navigate folders to find the file they require. However, the range "B2:B14" will always be the same, in every file.

Thanks :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You need to continue this in your new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,870
Messages
5,627,359
Members
416,245
Latest member
Xterminat

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