A way - probably, but it's at least modestly painful. These comments
are without having ever done this or any testing, so take them with a
rather large grain of salt.
Having said that, check if a combobox's listfillrange can be specified
as a named range. If it can, you can move the full list of choices into
a named range (FullList), another named range (CurrentList), and have
other named ranges set up for each of the comboboxes (ComboList_1
through ComboList_5). All named ranges start out as copies of
FullList.
Then, when an arbitrary item in an arbitrary combobox is selected,
write code in its _Click event to remove the selected item from
CurrentList and to set the named ranges for the other comboboxes(but not
the one where the selection was just made or any ComboBox that already
has a select!) to be the same as the updated CurrentList.
Of course, if the use de-selects an item, you would have to add it back
to CurrentList and update all comboboxes that don't have a current
selection.
Things would be easier if you could count on the user first making a
selection in ComboBox1, then in ComboBox2, etc - but you know how users
are. You could actually enforce that by having all the ComboBoxes
disabled, except for the first, and using the _Click event of ComboBox1
to enable ComboBox2 and so on. You would still have to dynamically
update the named range associated with the next ComboBox to be
selected.
This breaks down, though, once all ComboBoxes have been selected - then
the user can change any one, and you're back to the complexity of having
to manage all of the named ranges any time any ComboBox selection is
changed. Unless, of course, at the same time you enable ComboBox2
because a selection has been made in ComboBox1 you disable ComboBox1.
Extending this logic means that there is always one and only one
ComboBox for which a selection can be made, but now you have to have an
item in the dynamic named ranges that, when selected, disables the
current ComboBox and enables the previous one.
Most complex! I wouldn't start this late at night without a LOT of
whatever caffeine delivery system you prefer.
All in all, you might take a look at OptionButtons and see if they can
do what you want - might be a lot simpler.
Good luck on this :Bgr and be sure to post back on how it went/what
worked if you do tackle it!
