Preventing manual entry in dropdown list ... possible?

[Maurice]

I have created a Worksheet which has a number of cells with dropdown lists.
Now I have noticed that when such a cell is unlocked, people can manually
type into those cells as well. There is data validation in place which will
bounce any input not listed, but I would rather prefer to be ahead of the
data validation and only allow people to select the entries in the respective
lists. Initially I tried to mess around with various Excel settings (data
validation, etc...) and then with VB but I just can't seem to figure out if
it's even possible.

Is it possible to somehow prevent people from manually entering anything
into those cells, forcing them to make a pick from the list in the cell's
dropdown box? And if so, how can I do this?

[Toppers]

Maurice,
I don't quite understand why the standard Data Validation
using a List will not suffice as this (can/does) prevent entry of data not in
the DV list. Am I missing something or just dumb?

"Maurice" wrote:

> I have created a Worksheet which has a number of cells with dropdown lists.
> Now I have noticed that when such a cell is unlocked, people can manually
> type into those cells as well. There is data validation in place which will
> bounce any input not listed, but I would rather prefer to be ahead of the
> data validation and only allow people to select the entries in the respective
> lists. Initially I tried to mess around with various Excel settings (data
> validation, etc...) and then with VB but I just can't seem to figure out if
> it's even possible.
>
> Is it possible to somehow prevent people from manually entering anything
> into those cells, forcing them to make a pick from the list in the cell's
> dropdown box? And if so, how can I do this?

[Tom Ogilvy]

In xl2000 and earlier, if you locked the cells and protected the sheet, as I
recall, you could still use the data validation dropdown to update the cell.
I don't have a copy of those versions to test, so I can't verify it; but that
is my recollection.

I believe this behavior has changed and you can no longer edit the cell in
xl2002 and later. (which personally I think is the wrong way to go).

--
Regards,
Tom Ogilvy


"Maurice" wrote:

> I have created a Worksheet which has a number of cells with dropdown lists.
> Now I have noticed that when such a cell is unlocked, people can manually
> type into those cells as well. There is data validation in place which will
> bounce any input not listed, but I would rather prefer to be ahead of the
> data validation and only allow people to select the entries in the respective
> lists. Initially I tried to mess around with various Excel settings (data
> validation, etc...) and then with VB but I just can't seem to figure out if
> it's even possible.
>
> Is it possible to somehow prevent people from manually entering anything
> into those cells, forcing them to make a pick from the list in the cell's
> dropdown box? And if so, how can I do this?

[Maurice]

> Maurice,
> I don't quite understand why the standard Data Validation
> using a List will not suffice as this (can/does) prevent entry of data not in
> the DV list. Am I missing something or just dumb?

The validation is there; whenever the user types something into the cell on
the keyboard, the input is validated against what is in the list of the
cell's dropdown box. Anything entered that is not in the list is rejected.

What I want to do, however, is to reject any and all keyboard input into the
cells with the dropdown list, before it is even entered - and thus force the
user to make a selection from the list using only the mouse.

[Toppers]

In XL2003 you have the choice of restricting the entry to the contents of a
list OR allowing input of any value in addition to the DV list.

"Tom Ogilvy" wrote:

> In xl2000 and earlier, if you locked the cells and protected the sheet, as I
> recall, you could still use the data validation dropdown to update the cell.
> I don't have a copy of those versions to test, so I can't verify it; but that
> is my recollection.
>
> I believe this behavior has changed and you can no longer edit the cell in
> xl2002 and later. (which personally I think is the wrong way to go).
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Maurice" wrote:
>
> > I have created a Worksheet which has a number of cells with dropdown lists.
> > Now I have noticed that when such a cell is unlocked, people can manually
> > type into those cells as well. There is data validation in place which will
> > bounce any input not listed, but I would rather prefer to be ahead of the
> > data validation and only allow people to select the entries in the respective
> > lists. Initially I tried to mess around with various Excel settings (data
> > validation, etc...) and then with VB but I just can't seem to figure out if
> > it's even possible.
> >
> > Is it possible to somehow prevent people from manually entering anything
> > into those cells, forcing them to make a pick from the list in the cell's
> > dropdown box? And if so, how can I do this?