Userform with Cascading Combo Boxes Based on Dynamic Named Ranges...

Hello Everyone,

This is my first time posting here, so right off I'd like to say thank you for all of the great questions and answers here that have helped me so much already. I'm quite new to VBA, so this has been a lifeline for me.

I am creating a template workbook with many tables which contain multiple dependent dropdown lists. These cascades appear to be working just fine in the tables via data validation using the INDIRECT function.

My challenge has been to create this same functionality in the userforms.

I have found several examples of code for similar situations to mine, but each one is just unique enough that I’m not able to recognize how to make it work for my particular use (yes, very new to VBA).

The Context:

To keep it simple, here are just some basic details for one of the tables…

I’ll call this the MAIN table, and it has 3 columns: 1. “Type” 2. “Subtype” 3. “Item”

1. The “Type” column has a drop-down list in it based on a single-column dynamic named range which is stored on a different sheet in the workbook.

2. The “Subtype” column is dependent on the user’s drop-down list choice in the “Type” column and it draws on dynamic named ranges with names that exactly match the options in the “Type” column list. (These ranges are also housed on that other worksheet.)

3. Similarly, the “Item” column is dependent on the user’s drop-down list choice in the “Subtype” column and draws on dynamic named ranges with names that exactly match the options in the “Subtype” column lists.

So all named ranges are dynamic, single-column, and are stored on a sheet named “DropDownLists”. (The ranges are directly based on named tables.)

The Userform:

1. The “Type” field is a combo box that has already been coded to show all items in the named range called “rgeMAINItemType”. That seems to be working fine.
Name: cboTypeMAIN

2. The “Subtype” field is a combo box that SHOULD BE populated by the range whose name matches the selection the user enters into cboTypeMAIN.
Name: cboSubtypeMAIN

3. The “Item” field is a combo box that SHOULD BE populated by the range whose name matches the selection the user enters into cboSubtypeMAIN.
Name: cboItemMAIN

I won’t post any of the code I have been trying, in order to not confuse the matter here. Also, I realize that there might be potentials for errors that one should address within the coding that I am just not aware of...

If anyone is able to assist me with a solution, it would be very greatly appreciated. (I have been working on this without success for a very long time!)

(Simple workbook example attached.)

Thank you very much!!

Hi Beth,

Thank you very much for your reply! Your code worked beautifully on that sample file that I had posted.

On my actual file (which uses the same names), when I paste the code into the userform module the form no longer opens and "Compile Error: Variable not defined" pops up. It points to "ws" on the following line:

Set ws = Sheets("DropDownLists")

Since I used a similar line in the UserForm_initialize() procedure without problem, I tried changing the above line to match that, using the following 2 lines:

Dim ws As Worksheet
Set ws = Worksheets("DropDownLists")

But that brings up a new error message, "Runtime error 9: Subscript out of range", regarding the following line of your code:

.List = ws.ListObjects("tbl" & cboTypeMAIN).DataBodyRange.Value

Would you happen to know what this is about?
(If these are simple issues, I do apologize for being so clueless!)

Thanks again -
Hilary

Is there definitely a table with the name of the item selected in the first ComboBox? And how many items are in the list of that table?

Beth.

I suspect at least one of your tables only has a single value and that's throwing up the error as you cannot add a single value to a ComboBox using .List

However, as I mentioned earlier there are several ways of doing this, and here's another. This time looping through all the values in the tables and adding them one by one.
Bit slower, but nothing you'll notice unless your tables have thousands of values.

Replace the code you added earlier with this:

Please Login or Register  to view this content.

Beth.

Any chance we could see a desensitized version of the real file?

Far easier to diagnose the issue if we can see it in context.

Beth.

Hi Beth,

Here is all the VBA code from the UserForm module for the MAIN table. Could this help? (...rather than me posting the actual workbook...)

This userform is still a work in progress on multiple fronts, and unfortunately you will be able to see clearly what a beginner I am with coding...
It is more than likely that there is something I've put in there which is causing conflict with the code you have supplied...

If this is too convoluted to look at, please let me know. I have already taken up much of your time...
Thank you -
Hilary

We were all beginners at some point... Having looked at your code it's well written and doesn't look like beginner work, what it unfortunately doesn't tell me is what's causing the error.
Would be much easier to find that with the workbook.

Beth.

Okay, that certainly does make sense. I'll need to take a bit of time to sort that out so I can post a clean version.

In the meantime, in case it is relevant, the tables which are sitting on the "DropDownLists" sheet have actually been loaded in from another workbook via a query. I'm not sure if that would have an effect on this situation...

For now, thanks very much for all of your assistance so far...

Hilary

No problem. Happy to help.

I doubt the lists being imported via a query will be the issue, but cannot say for sure without seeing the file I'm afraid...

Beth.

Hi Beth,

It’s working!

With a little time to go over everything again, I discovered that I had in fact made the exact error that you had originally suspected.

When you first mentioned it, I double-checked and compared all the list options with the table names and named ranges. But I managed to confuse the table/range names, so I had the table names matching the list options, rather than the named ranges matching them. Once I switched those out, it all worked beautifully.

I just can’t thank you enough for your help. Both sets of code that you posted work perfectly. Thank you for seeing this through with me. This project can now move forward again!

Sincerely,
Hilary

Very glad I was able to help, Hilary.

Good luck with the rest of the build and you know where we are if you get stuck.

Beth.