Populating combobox 2 with items that match criteria from combobox 1

Hello,

Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.

if column a = bears and column b = colours of bears then

when I select bears in combobox one, combobox 2 would populate with colors of bear.

I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.


I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.

Please advise

okay, this is what I mean,

looking at a list with two levels of classification, it will populate the 2nd combobox with only the items that match the criteria specified in the 1st combobox

eg. If I select Big Cats in the 1st combobox, the 2nd combobox will offer the choices of those rows that have "Big Cats" in the first column, thus offering choices like "lynx","lion" etc.

I am trying it this way as the real sheet as at least 200 items and I'll be darned if I'm gonna make that into a dependant list setup.

Try this code in the combobox1 change event

Please Login or Register  to view this content.

wow. Thank you. Exactly what I was looking for.

Question, could you explain the code a little bit,

especially the portion with references to B1 and B2


and what is r mean?

BigBas,

I've used your example as it will be easier on the user to add items rather then dealing with separated lists.

the problem is that:
1) for criteria that acutally do not have any items in the list, the macro hangs and excel crashes. E.g. if I select "dogs" and there is only "Big Cats" and "Bears" in the list, error occurs.

Can you help with some error handling code so that combobox2 says "none availible" if that error occurs?

2) can an evaluation portion be added so that:
if the 7th column of r = "availilble" then it gets added to combobox2,
else it doesn't get added?

If you could provide any insight on this, I would be most appreciative

Kuraitori:

There are going to be a few approaches to best resolving your problem; it will be based on which works best for you.

1. I personally think your best bet is to limit combobox selections to options in the list. This way, an end user cannot select an option that is not on the list, thereby resolving the problem. If you absolutely must allow that a user type in unavailable options, I have added an amended code. Instead of entering the code in the combobox_change() even, it will be entered in the combobox_lostFocus() event. The disadvantage to this is that the code does not fire until the entry in completed. In order to be complete, the user will have to click outside of the textbox.

Please Login or Register  to view this content.

2. I'm not sure I understand what you need. Can you please update your dummy data sample so I can see what you are looking for?

Hi - I am a bit new to VBA but have some background in programming so I think I can get this solution to work for me as well. The only problem I have here is that I need the combobox to look and act just like it would if it was a data validation list. The content of what is selected in the combobox is used by several functions throughout the workbook. How do I use this this solution given my situation?

Hi there forum users! I'm acctually not and programer, but i have this smal job to be done and i found, that BigBas macro would work perfectly for this. But i'm struggling with my smal VBA knowledge to do two things: First split data between two sheets. List for Combobox1 is in Sheet1, and for Combobox2 in Sheet2. Both comboboxes are in Sheet1. Second thing is that if user extends the range with new data, he would have to update VBA code. Is there a way to put some code for checking the last row with data for both list.
It's a little bit old thread, but i hope someone could help me. Thank you in advance