I've got an unusual problem that may not have a solution, but wondering if anyone knows for sure...
I have a Userform which includes 5 fields that are chosen from a list of, say, 15 choices. The main list (15 choices) is fixed. But, for example, if the user selects "Apples" in the first field, then the dropdown list in the second field will only have 14 choices (with "Apples" removed, so the user can't make the same choice in two of the fields). Likewise, after the user selects the second field, that choice (as well as "Apples") gets removed from the dropdown list for field 3, etc.
In addition, the dropdowns for fields 2 thru 5 show only ONE choice (a "blank", empty choice) until a selection is made in the field immediately before it. This prevents the user from making a selection (for example) in field 2, before field 1 has been entered.
I've already created 5 separate dynamic lists (for the 5 dropdowns), and they function as I've intended. But I have a problem. When the Userform is opened, the lists for fields 2-5 contain only one entry (the blank entry). When the user makes a selection in field 1, the physical list for field 2 is correctly updated (to a 14-entry list), and the named range (that's the RowSource for field 2 in the Userform) is correctly updated from a 1-cell range to a 14-cell range. But apparently the Userform only evaluates RowSource when the Userform is first opened, because the dropdown for field 2 still only shows one entry. It shows the first entry (of the 14 desired choices), but ONLY that first entry. So the Userform thinks RowSource is still referring to the original (1-cell) named range.
Is there any way to force the Userform to re-evaluate the RowSource named range(s) for fields 2-5, while the Userform is open?
Bookmarks