Hi All,

First of all, thank to them who take the time to look at this. I am currently making a database in Excel where I want to list several companies. To add a new company to the list, I want to use a userform (As given in the example file attached). Three of the fields that have to be filled out are "Super Category", "Major Category" and "Sub Category". In order not to make thousands of different names or designations, I would like to give the people choices using ComboBoxes. The purpose is to link them all three to each other. The content of Sub Category has to be based on the content of the Major Category and Major Category will, on its turn, be based on the choices made in the Super Category field.



The data with the different categories used for the ComboBoxes is located on sheet 1 in the example file attached.

For example: if a person chooses "Baby" as Super category, the only items that may appear as Major categories should be: baby food, diapers, baby wipes, formula, beby health, baby accessories and petroleum jelly.
If baby food is chosen as major category, only 'baby food jar', 'baby food dry, cereal', 'baby food toddler snacks', baby food juices' and 'O&N baby food' can appear.

The problem I have right now is with the code to link the Comboboxes and provide them with the right content. Can anyone help me on this code and tell me how I can connect the boxes and make sure that they contain the values based on each other value?

Thanks in Advance,
Thomas

Attached Files

example.xlsx (302.0 KB, 9 views)

Very basic, step-by-step instructions. You can always expand from this example:

http://www.excel-easy.com/vba/exampl...mbo-boxes.html

Thank you for your response
The only disantvantage I have is that I have more than 1000 items that I have to put in the combobox... I am looking for more like a general code, so I don't have to do iT manually line boy line.

It's late here now. Going to get some sleep.

You might try searching for POPULATE COMBOBOX WITH ARRAY and see what that turns up. Not certain if it can be done (I haven't had an occasion to try yet), but if you can
reference an array (a list of the terms you want to be displayed in the combobox) that
would reduce some of the typing. The array list could be placed in a hidden sheet for the
combobox to reference.

One way or the other, there is going to be some typing involved.

I'll check tomorrow to see what I can find also.

@ Logit
Certainly can, here's the goto page I have bookmarked.

@ thovdael
Have a look at this.
The first combo is populated from column A at the time of Userform_Initialization.
The second and third combos are populated by filtering based on the selections in the combos.
Hopefully this does what you're after and is commented enough that you can follow and adapt to your project.

Attached Files

example_thovdael.xlsm (322.9 KB, 20 views)

Thanks!!, this works perfectly

Thanks NoSparks !

You're welcome, glad I could help.

Be aware this solution is not a one size fits all.

It works fine here because the data is sorted and all the filtered rows are contiguous.
Also, the filtRng.Columns(x).Offset(1).SpecialCells(xlCellTy peVisible) always includes one extra row beyond the bottom of the filtRng which gets loaded into the drop down lists, hence the blank at the bottom. This prevents issues when there are no filter results but it may not always be desirable.