Cascading combo boxes have been done online and I had a problem recently which knocked me for 6 as we say here in Australia. Anyone who deals in cascading combo boxes will know only too well that if you are using a list and the Indirect formula to manage the combo boxes then the process can get very unruly very quickly. One of the issues with comboboxes is that they are set up to work in sequence. Combo box 1 feeds into combobox 2 which feeds combo box 3. The combo boxes need to be filled out in that order. Here is a thought to the discussion, what if you were setting up combo boxes and the user could pick any one of the comboboxes and have the boxes cascade from that point. Think of this example, say a user picked an item in combo box 1 but now only wanted the related items in combobox 4. Or let’s say they wanted to choose combobox 3 and have the combobox work based on the selection of combobox 1. To the best of my knowledge there are no tools online to cope with these scenarios.
I only started working on the problem as it came up at work and the criteria was very specific and I knew managing a list of information which was 4-5 comboboxes strong would be an absolute nightmare without coding. I wanted to create the information for each drop down on the fly. So as an item was chosen this formed the basis for all other combo boxes. Enter the scripting dictionary. Not many procedures run so quickly or isolate unique data so well.
So in summary what I am proposing is a linked set of comboboxes which is not dependent on being selected in a given order. With the following caveat – the first combobox governs the combo boxes. So rather than filling them out in a given order the first one needs to be populated first. If a department is chosen then the 3 sub departments are isolated to that parent department.
Take for example the following departments of a store.
Garden
Home
Clothing
IT
Auto
These appear in combo box 1 and dictate the other 3 combo boxes.
This is the top level. Beneath each level there are Categories, Sub Categories and Products. If you choose Garden (Combobox 2, 3 & 4) will have items which relate to the gardening department.
Once the category is selected in the first combo box any combo box one can be chosen thereafter. There is no cascading so you can choose 1 & 4, 1, 2 & 3 or any combination of the 4 combo boxes. As you narrow your search however the combo boxes have a cascading affect without them needing to be chosen from 1 to 4.
To maintain this sort of model it is about creating a master list of four columns with auto, clothing, garden, home and IT in the first column and their children in the following columns.
The above is a simple example showing the 4 categories. Maintaining this list is a far more palatable proposition than managing a list which spans 30-40 columns.
The following file outlines the procedure. The coding is in the file. If it appears to not work just reset the first combo box and you will find a full list of combo boxes once more.
Enjoy
This file has been saved as an XLS file it should work on later versions of Excel.
