Populating Listbox based on ComboBox selection - VBA

[JackDomino1]

Hi,

Im looking to populate a list box (5 coloums) based on the selection a user makes in a combobox.

However, the options in the combobox are not unique, so there will be multiple sets of data to display.

Name	Age	Hair Colour
Jack	34​	Brown
Ben	23​	Blonde
Steve	45​	Grey
Jack	65​	Black
John	19​	Black
Ben	21​	Brown

Names are select-able in the combobox. But if the user selects "Jack", the lstbox needs to display two rows of data.

I have managed to make it return the information associated to the first entry which matches the selection criteria, but I am struggling to get it to work on multiple entries.

Thanks

 

[Fluff]

Ok, try this change

Private Sub ComboBox1_Click()
   Dim Lst As Variant, Rws As Variant
   
   With Sheets("Main").Range("A1").CurrentRegion
      Rws = Filter(.Parent.Evaluate(Replace("transpose(if(@=" & Chr(34) & Me.ComboBox1 & Chr(34) & ",row(@),false))", "@", .Columns(1).Address)), False, False)
      Lst = Application.Index(.Value2, Application.Transpose(Rws), Array(1, 2, 3, 4, 5))
      If UBound(Rws) = 0 Then Lst = .Rows(Rws(0)).Value
   End With
   Me.ListBox1.List = Lst
End Sub

 

[JackDomino1]

Ok, try this change

Private Sub ComboBox1_Click()
   Dim Lst As Variant, Rws As Variant
  
   With Sheets("Main").Range("A1").CurrentRegion
      Rws = Filter(.Parent.Evaluate(Replace("transpose(if(@=" & Chr(34) & Me.ComboBox1 & Chr(34) & ",row(@),false))", "@", .Columns(1).Address)), False, False)
      Lst = Application.Index(.Value2, Application.Transpose(Rws), Array(1, 2, 3, 4, 5))
      If UBound(Rws) = 0 Then Lst = .Rows(Rws(0)).Value
   End With
   Me.ListBox1.List = Lst
End Sub

Same error for the line:
Lst = Application.Index(.Value2, Application.Transpose(Rws), Array(1, 2, 3, 4, 5))

 

[Fluff]

Are you running that from the combo Click event?

 

[JackDomino1]

Are you running that from the combo Click event?

Yup

 

[Fluff]

Ok when you get the error have a look at the Locals Window (Click view, then locals window) what does it show for Rws?

 

[JackDomino1]

 

[Fluff]

That shows you are running the code from the change event & not the click event.
It really needs to be in the click event.

 

[JackDomino1]

Same thing if you run it as click event. I was originally running as click, but swapped to "change" to see if it helped.

 

[Fluff]

In that case the value in the combo cannot be found in col A of you sheet.
Did you change the sheet name in the code to match your actual sheet name?

 

[JackDomino1]

In that case the value in the combo cannot be found in col A of you sheet.
Did you change the sheet name in the code to match your actual sheet name?

Yep, I did.

However, I have just copied the code into a new, blank workbook and it works perfectly. So there is something else in my main project workbook which is causing things to act out. Any ideas what it could be?