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]

How about

Private Sub ComboBox1_Click()
   Dim Lst As Variant, Rws As Variant
   
   With Sheets("Main").Range("A1").CurrentRegion
      Rws = Filter(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]

Thank you, just tested it out.

Lst = Application.Index(.Value2, Application.Transpose(Rws), Array(1, 2, 3, 4, 5))

This line is flagging runtime error "13"

 

[Fluff]

Are your names in col A starting in A2 with a header in row1?
Also do you have any blank rows or columns in the data?

 

[JackDomino1]

Yes, names are in col A, with a header in A1.

No blank rows or cols

 

[mikerickson]

I would just put the same list in both controls and alter the .ColumnWidths properties.

Private Sub Userform_Initialize()
    ComboBox1.ColumnCount = 2
    ListBox1.ColumnCount = 3
    ListBox1.List = Range("A1:C6").Value
    ComboBox1.List = Range("A1:C6").Value
    ComboBox1.ColumnWidths = ";0;0"
    ListBox1.ColumnWidths = "0;;"
End Sub

Private Sub ComboBox1_Change()
    ListBox1.ListIndex = ComboBox1.ListIndex
End Sub

 

[Fluff]

How are you loading the combobox?

 

[JackDomino1]

How are you loading the combobox?

Private Sub UserForm_Initialize()
'populates combobox with avaliable XP
Dim z As Integer
z = 3
While ActiveWorkbook.Sheets("Data").Cells(z, 2) <> ""
UserForm14.ComboBox1.AddItem (ActiveWorkbook.Sheets("Data").Cells(z, 2))
z = z + 1
Wend
End Sub

 

[Fluff]

You're loading the combo from col B, but you said your names were in col A.

 

[JackDomino1]

You're loading the combo from col B, but you said your names were in col A.

Yes, so the names in Col A are not unique and I didn't want duplicates appearing in the combobox. So i created a separate, unique list to populate the combobox with (stored in a different sheet). This is probably an overly complicated way to do it.

However, bypassing this and using Col A to populate the combobox still flags the same error