Hi all,

I have a worksheet with data validation set up on 3 cells
containing "in cell" dropdown lists which derive their
data from named ranges which reside in another (hidden)
worksheet. I am looking for some code that will
programatically allow a user to add new entries to the
named range associated with each dropdown list and also
to select an existing entry and delete it if necessary.

Clear as mud...? It is to me but i'm sure all you
geniuses will find this a breeze.

Thanks heaps.

How would you want the user to indicate that they wanted to do this? How
would they indicate what they wanted to add or which item they wanted to
delete. Would you have a special cell with the validation applied but the
warning disabled? (so the user could make a new entry).

When an entry is removed, can the cell in the hidden sheet containing that
entry be deleted (with the other data shiftin up) or will this impact data
in other parts of the sheet, so the entries must be cleared and the
following entries copied up to fill the hole.

--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
Hi all,

I have a worksheet with data validation set up on 3 cells
containing "in cell" dropdown lists which derive their
data from named ranges which reside in another (hidden)
worksheet. I am looking for some code that will
programatically allow a user to add new entries to the
named range associated with each dropdown list and also
to select an existing entry and delete it if necessary.

Clear as mud...? It is to me but i'm sure all you
geniuses will find this a breeze.

Thanks heaps.

g'day Tom

Thanks for the response....

A user simply types an entry into the cell associated
with the dropdown list. Data validation detects the entry
as not being part of the list and prompts the user as
such with a question "Do you wish to Add to list?" If
they respond "YES" the code kicks in and adds what they
typed to the list. If they respond "NO" normal data
validation processes apply.

On the deletion side, the cell containing the item to be
deleted is removed and the named range resized
accordingly.

Hope this helps.


regards,
Neal
-----Original Message-----
How would you want the user to indicate that they wanted
to do this? How
would they indicate what they wanted to add or which
item they wanted to
delete. Would you have a special cell with the
validation applied but the
warning disabled? (so the user could make a new entry).

When an entry is removed, can the cell in the hidden
sheet containing that
entry be deleted (with the other data shiftin up) or
will this impact data
in other parts of the sheet, so the entries must be
cleared and the
following entries copied up to fill the hole.

--
Regards,
Tom Ogilvy

"Neal" wrote in
message
...
Hi all,

I have a worksheet with data validation set up on 3
cells
containing "in cell" dropdown lists which derive their
data from named ranges which reside in another (hidden)
worksheet. I am looking for some code that will
programatically allow a user to add new entries to the
named range associated with each dropdown list and also
to select an existing entry and delete it if necessary.

Clear as mud...? It is to me but i'm sure all you
geniuses will find this a breeze.

Thanks heaps.


.

Data validation doesn't work that way.

--
Regards,
Tom Ogilvy

"Neal" wrote in message
...
g'day Tom

Thanks for the response....

A user simply types an entry into the cell associated
with the dropdown list. Data validation detects the entry
as not being part of the list and prompts the user as
such with a question "Do you wish to Add to list?" If
they respond "YES" the code kicks in and adds what they
typed to the list. If they respond "NO" normal data
validation processes apply.

On the deletion side, the cell containing the item to be
deleted is removed and the named range resized
accordingly.

Hope this helps.


regards,
Neal
-----Original Message-----
How would you want the user to indicate that they wanted
to do this? How
would they indicate what they wanted to add or which
item they wanted to
delete. Would you have a special cell with the
validation applied but the
warning disabled? (so the user could make a new entry).

When an entry is removed, can the cell in the hidden
sheet containing that
entry be deleted (with the other data shiftin up) or
will this impact data
in other parts of the sheet, so the entries must be
cleared and the
following entries copied up to fill the hole.

--
Regards,
Tom Ogilvy

"Neal" wrote in
message
...
Hi all,

I have a worksheet with data validation set up on 3
cells
containing "in cell" dropdown lists which derive their
data from named ranges which reside in another (hidden)
worksheet. I am looking for some code that will
programatically allow a user to add new entries to the
named range associated with each dropdown list and also
to select an existing entry and delete it if necessary.

Clear as mud...? It is to me but i'm sure all you
geniuses will find this a breeze.

Thanks heaps.


.