On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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...)

--
Garry


Yes, each new drop down added will have the updated named range list which is exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range Cable_1.

I will then write some alert code.

Howard

On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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...)

--
Garry


Yes, each new drop down added will have the updated named range list which is
exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will
have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range
Cable_1.

I will then write some alert code.

Howard

Because your range is dynamic, each existing dropdown that refs that range will
auto-update their lists AS THE RANGE IS EDITED! What's to stop a user from
changing a previous selection?

--
Garry

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

On Thursday, December 28, 2017 at 10:19:02 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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...)

--
Garry


Yes, each new drop down added will have the updated named range list which is
exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will
have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range
Cable_1.

I will then write some alert code.

Howard

Because your range is dynamic, each existing dropdown that refs that range will
auto-update their lists AS THE RANGE IS EDITED! What's to stop a user from
changing a previous selection?

--
Garry

At present, nothing. If there is a "mistake" or a inadvertent change to a previous entry, clearing all drop downs will reset the entire named range.

Howard

On Thursday, December 28, 2017 at 10:19:02 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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...)

--
Garry


Yes, each new drop down added will have the updated named range list which
is exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will
have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range
Cable_1.

I will then write some alert code.

Howard

Because your range is dynamic, each existing dropdown that refs that range
will auto-update their lists AS THE RANGE IS EDITED! What's to stop a user
from changing a previous selection?

--
Garry

At present, nothing. If there is a "mistake" or a inadvertent change to a
previous entry, clearing all drop downs will reset the entire named range.

Howard

Clearing the dropdowns only removes the cell contents, not the range they ref.
How will a changed dropdown reset the range?

--
Garry

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

On Thursday, December 28, 2017 at 10:48:52 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 10:19:02 PM UTC-8, GS wrote:
On Thursday, December 28, 2017 at 9:51:51 PM UTC-8, GS wrote:
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...)

--
Garry


Yes, each new drop down added will have the updated named range list which
is exactly what I want. (NOT allow an item to be selected more than once)

The first drop down, (starter) will have say 12 entries. The second will
have only 11 and so on until the last will show only 1.

All I need is a method to count the remaining items in the named range
Cable_1.

I will then write some alert code.

Howard

Because your range is dynamic, each existing dropdown that refs that range
will auto-update their lists AS THE RANGE IS EDITED! What's to stop a user
from changing a previous selection?

--
Garry

At present, nothing. If there is a "mistake" or a inadvertent change to a
previous entry, clearing all drop downs will reset the entire named range.

Howard

Clearing the dropdowns only removes the cell contents, not the range they ref.
How will a changed dropdown reset the range?

--
Garry


Formulas on the other sheet. All the items are restored to the named range column. It is a fairly common "...one selection only from a drop down list" usage.

Howard

Formulas on the other sheet. All the items are restored to the named range
column. It is a fairly common "...one selection only from a drop down list"
usage.

Are you refering to a "Hide Used Items in a Dropdown List" implementation such
as exampled on the Contextures website?

--
Garry

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

On Friday, December 29, 2017 at 1:59:03 AM UTC-8, GS wrote:
Formulas on the other sheet. All the items are restored to the named range
column. It is a fairly common "...one selection only from a drop down list"
usage.

Are you refering to a "Hide Used Items in a Dropdown List" implementation such
as exampled on the Contextures website?

--
Garry


I don't know if that is true or not.

I have no intentions to change the method of the drop downs, my post here was to find a way to count the number of items in a named range.

I am unable to make the solutions offered work for me, but I will revisit them.

Thanks for looking in.

Howard