On Thursday, December 28, 2017 at 6:48:08 PM UTC-8, GS wrote:
Hi Howard,
So if I understand correctly, you want to have a fixed number of cells
contain unique entries in the Cable_1 list and remove those entries once
assigned to a cell until all entries in the list are used, thus
destroying the dynamic range.

Using your _Change event should work fine for creating the dropdown in the
cell below Target. What you also need to do is delete the selected entry
from the Cable_1 dynamic range (Shift=xlUp) so the next dropdown doesn't
include the selected item.

When the list is empty, the _Change event needs to know not to ask "Add
Dropdown?"

Another way:
Have your _Change event open a dialog that lets the user make selections
from a combobox to populate the intended cell with no opportunity to
change the selection after the dropdown has been used to avoid messing up
control over the items remaining in the list.

-- Garry


Hi Garry,

Yes to this question.
When the list is empty, the _Change event needs to know not to ask "Add
Dropdown?"

Ref: Removing items from Cable_1 as used.
On another sheet the named range is a product of this formula, pulled down.
Where it provides a contiguous list of non selected items.

=IF(ROW(A2)-ROW(A$2)+1COUNT(B$2:B$13),"",INDEX(A:A,SMALL(B$2: B$13,1+ROW(A2)-ROW(A$2))))

Whenever an item is selected in a drop down, it is removed from Cable_1,
(by the formula referencing other columns, A:A & B2:B13)

The formula makes a list from row 2 and on down for all remaining items yet
to be selected.

Howard

Ok, but that approach makes all entries in those dropdowns invalid because
each dropdown will auto-update to contain the new list. A user could change
it and thus mess up your control over the remaining list. Using a dialog (as
a popup window) as I described eliminates the ability to change the dropdowns
after the selection has been made. (just saying...)

...because the dialog puts the list item into the cell, ergo no dropdown!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion