Drop Down Question

[jonlamb31]

I am producing a survey in excel. I want to use drop down menus. I have protected the sheet in the normal way, but seem unable to prevent users copying the entry from the drop down list in column 1 and pasting the answer over the entries in column 2, which has a different validation list and drop-down menu.

I have been trying to use the error message, but that cannot stop a copy and paste. I have also tried to use conditional formatting, but to no avail.

Can anyone help please?

Thanks,

Jon

 

[venkat1926]

when you copy and paste, not only the cellvalue but also validation of that cell (sort of formatting) is copied. so you have to use some sort of IF function dpending upon your conditions for the second cell

 

[Peter_SSs]

jonlamb31

Welcome to the Mr Excel board!

I believe that one way would be to use some VBA code to disable the ability to paste (at least in your drop-down list cells). Unfortunately, I am not the one to provide the 'how to' details but there are plenty of VBA experts arond this board and I expect that one of them will pick this up and provide you with a suggested solution.

Just in case nobody else does chime in with the VBA solution, here is a possible (not-very-good) alternative.

Suppose a drop-down list is in B2 and the Data Validation list is drawn from E2:E4 as shown below. In C2 you could put the formula =IF(ISNUMBER(MATCH(B2,E2:E4,0)),"","Click 'undo' and choose from the drop-down list") and you could also use Conditional Formatting to colour C2 if not equal to "". This will not stop the paste, but hopefully(?) might get the user to undo it and choose from your drop-down list.

In the example, I have copied 'water' from A2 and pasted in B2 where I should have been choosing Red, Blue or Yellow from a drop-down list.

Mr Excel.xls
	A	B	C	D	E
1		Choose Colour			B2 Drop-Down choices
2	water	water	Click 'undo' and choose from the drop-down list		Red
3					Blue
4					Yellow
Sheet1