Drop Down Question

jonlamb31

New Member
Joined
Nov 10, 2005
Messages
1
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,552
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Choose ColourB2 Drop-Down choices
2waterwaterClick 'undo' and choose from the drop-down listRed
3Blue
4Yellow
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,763
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top