Populating Listbox based on ComboBox selection - VBA

JackDomino1

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

NameAgeHair 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,966
Office Version
  1. 365
Platform
  1. Windows
Ok, try this change
VBA Code:
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Ok, try this change
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,966
Office Version
  1. 365
Platform
  1. Windows
Are you running that from the combo Click event?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,966
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,966
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Same thing if you run it as click event. I was originally running as click, but swapped to "change" to see if it helped.
1600103721009.png
 

Fluff

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

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

Watch MrExcel Video

Forum statistics

Threads
1,130,292
Messages
5,641,384
Members
417,207
Latest member
Vxhaet

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