VBA Combobox options to be filtered by three other comboboxes

[JackDomino1]

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]

Ok, replace Combo4 & the initialize with

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

 

[Fluff]

The above will also cure the problem for part 1

 

[JackDomino1]

The above will also cure the problem for part 1

Works perfectly! Thank you for your time this afternoon

 

[Fluff]

You're welcome & thanks for the feedback

 

[JackDomino1]

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]

How about

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]

How about

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]

My pleasure & thanks for the feedback

 

[JackDomino1]

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]

You need to continue this in your new thread. Thanks