Use Named Range to set ComboBox List?

Try this one.

Please Login or Register  to view this content.

If I recall from the other thread, you have a series of combo boxes and the goal is that the user chooses an item from the first, the second is populated with all the entries from the first less the one selected, and so on.

1) To fill a Combobox with a named range, dynamic or fixed. you can use code like this

Please Login or Register  to view this content.

Note that this assumes that DynamicRange is a single column or values.
Note also that no method of filling a combo box will automaticaly update as the size of the dynamic range changes. You must call your "fill the combo box" code every time that the values in the named ranges changes.

2) To get cascading combo boxes, with each box showing the unselected values from the previous boxes:

I would make the controls so the the .Tag property of a control had the name of the subordinate combo box.
So ComboBox1's .Tag property would be "ComboBox2". ComboBox2's .Tag property would be "ComboBox3", etc.
This would be done at Design time.

I would then use code like this in the user form's code module.

Please Login or Register  to view this content.

In the attachment, I used a Dynamic Named Range to fill the initial box. Also, the user form uses ListBoxes rather than combo boxes (so one can see the list without having to click the drop button). The coding is the same for both List and ComboBoxes.

Mikerickerson,
Unfortunately, when I try:

Please Login or Register  to view this content.

I get:

Run-time error -2147352571 (800200005)':
Could not set the Value property. Type mismatch.

My dynamic list refers to 16 contiguous cells in a single column, that are all in General format and contain text. How can this give me a "type mismatch"?

And I should be setting the LIST for the ComboBox, not its VALUE, right? But, as noted earlier,

Please Login or Register  to view this content.

doesn't work either (gives me Run-time error '70': Permission denied).

Your sample worksheet works fine, but mine doesn't. I'm guessing it must be in how I'm defining the dynamic range. I'll try using a definition more like your sample and see if that works.

UPDATE:
Tried changing my dynamic range definition, it still bombs with the Run-time error 70: Permission denied. Any idea why I get this error?
My (new) dynamic range definition is:

Please Login or Register  to view this content.

FINAL (Hopefully!) UPDATE:
I figured out why I'm getting the Error 70. I still had the RowSource property of my ComboBox filled in, so it wouldn't let me redefine the list. [Smack Forehead!!]
I had previously deleted the RowSource, but I must have closed the file without saving that change! Sorry for wasting everyone's time!!

If the COUNTA evaluates to 0, then the named will return an error instead of a range.