Dynamic dependant VBA drop downs

[questionguy]

I have a user form with a two combo boxes wheras one reads 'Main Category' and the other 'Sub Category'.

To make it simple here is a little preview:

Main Category: Plumbing
Sub Category: Showers, Toilets, Doors

Main Category: Electricity
Sub Category: Switches, Receptacles, Wires

The Code of my Main Category is as follows and works fine:

Please Login or Register  to view this content.

Is it possible tough to create a code that is dynamic (adding a new item will automatically show up in user form) and dependent (sub category is dependent on value of main category)?

*I tried Excel Data Validation, but it seems not to comply when using user forms...

[CRIMEDOG]

create name lists i.e. MainCategory: Plumbing, Electrical...
Sub Categories: Switches, Recepticles...

Then you can create cascading Combo Boxes on your user form.
Set up the initial Combo Box (MainCategoery) to load when the USer Form Opens,
Then the next (SubCategory) to Load when the first Cbo changes:

Please Login or Register  to view this content.

[questionguy]

Thanks a lot. Works perfect! Except some categories have more than one word, but I guess I have to include a SUBSTITUTE function when creating named ranges... Will see if that works...

[tigeravatar]

questionguy,

Here's an alternate method that doesn't use named ranges. Sample workbook attached. As a side note, the Categories table should always be sorted by Main Category.

[questionguy]

I like it, thanks!!

[CRIMEDOG]

Create MainCategory List and name the Range
Then Create a List for each Subcategory, and name each list a name from the MainCategory List.

[questionguy]

Thanks sharing your knowledge people...