Userform - ListBox items added based on Combobox value selected

[jayherring86]

Hello Everyone,

I am trying to make a userform to show a list of items that each person is holding in their "account". I have chosen to use a combobox to select the Account name, this is done.

I am having trouble with the listbox to show the list of items though. What would be needed is very similar to vlookup I think because the macro would take the name from combobox1, which is taken from a list on a sheet called "deposits", columnA. Listbox1 would simply need to look down columnA and show the item name, from columnC for each item with that account name.

Column A and Column C would go down to row 23000 and there is no limit to the number of items that can be attached to the same account - just in case that matters.

If any more information is needed, I'd be happy to provide it. Many thanks in advance

[mikerickson]

I think that something like this would work

Please Login or Register  to view this content.

[Tinbendr]

...simply need to look down columnA and show the item name, from columnC ....

Use a Label instead. You don't need a listbox to show one item.

[jayherring86]

Hi Tinbendr,

I actually do need the code to lookup more than one item. For each time the account name in ColA matches the one in ComboBox1, give the value of colC.

I will try your method mikerickson and report back.

[jayherring86]

Ok. I have tested it out and it doesn't work. I think it's something I'm doing rather than a fault in the code.

Where do i put the code in order to make it trigger and the right time? I tried it in ComboBox1 change, and click because i'm expecting that the listbox is to be filled when the combobox is changed by the user.

Also, what does the range D1 do? in range d1 there isn't any relevant information on my sheet?

[mehmetcik]

Hi

This is simple if you approach it right.

I just created a userform containing a listbox
I went into properties and set the listbox to two columns
I put some data into the spreadsheet
this code then fills the listbox with two columns of data:-

Please Login or Register  to view this content.

You can clear the listbox using

Please Login or Register  to view this content.

Ok to take this a bit further:

this code will populate the combobox automatically.

changing the combobox will update the list box.

Please Login or Register  to view this content.

[jayherring86]

I feel i need to clarify things.

I'm trying to make it so that if someone selects "John Smith" from the dropdown in combobox1 (which is populated from all unique names in colA), they are presented with a list of items that appear next to that name. So....

colA colC
John Smith Item1
Sarah Jane Item2
Jim Beam Item3
John Smith Item4

If john smith is selected, then listbox1 would be populated by Item1 and Item4.

[jayherring86]

Thank you Mehmetcik, this is getting very close to what I am looking to do.

Your code for the combobox change event is something I've never seen so I don't know where to begin altering it to look horizontally across the row, instead of looking for the header that matches Cbox1. Can you perhaps shed any light on that?

Thank you either way, I'm still learning when it comes to VBA and what you've given me there is very interesting

[mehmetcik]

Post a sample so I can see your data structure

[jayherring86]

Here's a sample file. You'll notice that the names I am using to populate combobox1 are actually just "=ColF". This is because of the way that the data in the list is being pasted in, I don't think that any code can take values in ColF and look backwards along a row - though i may be wrong.

I have actually provided the attachment with a simple userform that shows the code i'm currently using in it (sorry, i forgot to make a button to run it so you will have to open VisBasic to see it i'm afraid.

I think the existing code may be close, because when user selects "anonymous" the listbox develops a scroll bar, indicating to me that it is finding more rows of blank cells than it can show in the space it has.

Just so you know, all I need to show in the listbox is a list of ColC values attributed to the selected name, not any of the other information

Thanks again.

[mehmetcik]

Ok try this version:

[jayherring86]

What you've given me there works well, I did have to tweak it slightly though as the names were just for example and your code told the combobox to start with the name "Anonymous" showing. I have altered the code to look at F27 so that the word "Name" becomes that first entry.

I'm puzzled though, because I didn't touch some of the code that refers to "anonymous" in the userform initialize sub, and even when i changed the name anonymous in my table to something else, there was no error - what is

Please Login or Register  to view this content.

doing?

Please Login or Register  to view this content.

[mehmetcik]

I am ditching the name anonymous because I want that as the first entry
modify it to:-

Please Login or Register  to view this content.

If you are using name as your first entry

[jayherring86]

I understand. Thank you very much for your help.

I think this thread should now be marked as solved. Is that something I do? I can't find a button

[mehmetcik]

Sorry I don't know.

Look at the bottom of your post