Is there a way to allow users to select items from drop down lists, but also
to prevent them from being able to paste over the cells?

Non-VBA way. A little manual intensive upon setup, but once setup, should be
good.
Step 1: Create a variable, I called mine ErrCheck. Say for example I have 4
drop down lists, List1, List2, List3, List4, located in cells B3, B7, B11,
and B15 respectively. My ErrCheck formula might look something like this:
=AND(NOT(ISBLANK(B3)),COUNTIF(List1,B3)=0)*1+AND(N OT(ISBLANK(B7)),COUNTIF(List2,B7)=0)*1+AND(NOT(ISB LANK(B11)),COUNTIF(List3,B11)=0)*1+AND(NOT(ISBLANK (B15)),COUNTIF(List4,B15)=0)*1
Note: This is an array** formula (when entering, be sure to press
CTRL+Shift+Enter).

Step 2: Conditional Format the cells around your drop down lists (protected
cells).
For my examplse:
=AND($B3<"",COUNTIF(List1,$B3)=0) around B3
=AND($B7<"",COUNTIF(List2,$B7)=0) around B7
=AND($B11<"",COUNTIF(List3,$B11)=0) around B11
=AND($B15<"",COUNTIF(List4,$b15)=0) around B15
I chose red as it stands out. This will 'highlight' a cell that has
incorrect data in it.

Step 3: Data Validation. You want to ensure that there data validation stops
functioning for all lists, so I did the following:
DV for cell B3:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List1"))
DV for cell B7:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List2"))
DV for cell B11:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List3"))
DV for cell B15:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List4"))
This will shut down any drop down lists that they have. Now, obviously, the
user can still copy/paste over these cells. But I am under the assumption
that the users are not maliciously trying to mess up the data, and that it is
more on accident. I could make the cells surrounding all 4 drop downs red,
and leave it with invalid data if I really wanted.
I know it's a bit much, but it does at least tell the user where the error
is, and ostensibly, prevents the user from using any drop downs until they
get rid of the error. (Go to the red surround drop down list, and press the
delete key).





--
John C


"wew" wrote:

Is there a way to allow users to select items from drop down lists, but also
to prevent them from being able to paste over the cells?

I go through all that trouble, and not even a response? *sigh*
--
John C


"John C" wrote:

Non-VBA way. A little manual intensive upon setup, but once setup, should be
good.
Step 1: Create a variable, I called mine ErrCheck. Say for example I have 4
drop down lists, List1, List2, List3, List4, located in cells B3, B7, B11,
and B15 respectively. My ErrCheck formula might look something like this:
=AND(NOT(ISBLANK(B3)),COUNTIF(List1,B3)=0)*1+AND(N OT(ISBLANK(B7)),COUNTIF(List2,B7)=0)*1+AND(NOT(ISB LANK(B11)),COUNTIF(List3,B11)=0)*1+AND(NOT(ISBLANK (B15)),COUNTIF(List4,B15)=0)*1
Note: This is an array** formula (when entering, be sure to press
CTRL+Shift+Enter).

Step 2: Conditional Format the cells around your drop down lists (protected
cells).
For my examplse:
=AND($B3<"",COUNTIF(List1,$B3)=0) around B3
=AND($B7<"",COUNTIF(List2,$B7)=0) around B7
=AND($B11<"",COUNTIF(List3,$B11)=0) around B11
=AND($B15<"",COUNTIF(List4,$b15)=0) around B15
I chose red as it stands out. This will 'highlight' a cell that has
incorrect data in it.

Step 3: Data Validation. You want to ensure that there data validation stops
functioning for all lists, so I did the following:
DV for cell B3:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List1"))
DV for cell B7:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List2"))
DV for cell B11:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List3"))
DV for cell B15:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List4"))
This will shut down any drop down lists that they have. Now, obviously, the
user can still copy/paste over these cells. But I am under the assumption
that the users are not maliciously trying to mess up the data, and that it is
more on accident. I could make the cells surrounding all 4 drop downs red,
and leave it with invalid data if I really wanted.
I know it's a bit much, but it does at least tell the user where the error
is, and ostensibly, prevents the user from using any drop downs until they
get rid of the error. (Go to the red surround drop down list, and press the
delete key).





--
John C


"wew" wrote:

Is there a way to allow users to select items from drop down lists, but also
to prevent them from being able to paste over the cells?

Don't despair John

Maybe OP gave you a brownie point anyway..........if that's what you're
looking for.


Gord Dibben MS Excel MVP

On Tue, 12 Aug 2008 08:02:05 -0700, John C <johnc@stateofdenial wrote:

I go through all that trouble, and not even a response? *sigh*

Thanks John C

I didn't get the notification that I had received a response. Much
appreciated!

"John C" wrote:

Non-VBA way. A little manual intensive upon setup, but once setup, should be
good.
Step 1: Create a variable, I called mine ErrCheck. Say for example I have 4
drop down lists, List1, List2, List3, List4, located in cells B3, B7, B11,
and B15 respectively. My ErrCheck formula might look something like this:
=AND(NOT(ISBLANK(B3)),COUNTIF(List1,B3)=0)*1+AND(N OT(ISBLANK(B7)),COUNTIF(List2,B7)=0)*1+AND(NOT(ISB LANK(B11)),COUNTIF(List3,B11)=0)*1+AND(NOT(ISBLANK (B15)),COUNTIF(List4,B15)=0)*1
Note: This is an array** formula (when entering, be sure to press
CTRL+Shift+Enter).

Step 2: Conditional Format the cells around your drop down lists (protected
cells).
For my examplse:
=AND($B3<"",COUNTIF(List1,$B3)=0) around B3
=AND($B7<"",COUNTIF(List2,$B7)=0) around B7
=AND($B11<"",COUNTIF(List3,$B11)=0) around B11
=AND($B15<"",COUNTIF(List4,$b15)=0) around B15
I chose red as it stands out. This will 'highlight' a cell that has
incorrect data in it.

Step 3: Data Validation. You want to ensure that there data validation stops
functioning for all lists, so I did the following:
DV for cell B3:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List1"))
DV for cell B7:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List2"))
DV for cell B11:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List3"))
DV for cell B15:
Allow: List, Source: =INDIRECT(IF(ErrCheck0,"","List4"))
This will shut down any drop down lists that they have. Now, obviously, the
user can still copy/paste over these cells. But I am under the assumption
that the users are not maliciously trying to mess up the data, and that it is
more on accident. I could make the cells surrounding all 4 drop downs red,
and leave it with invalid data if I really wanted.
I know it's a bit much, but it does at least tell the user where the error
is, and ostensibly, prevents the user from using any drop downs until they
get rid of the error. (Go to the red surround drop down list, and press the
delete key).





--
John C


"wew" wrote:

Is there a way to allow users to select items from drop down lists, but also
to prevent them from being able to paste over the cells?