Protect data validation list from copy paste

I found this clever solution.
Prevent Copy/Paste over Data Validated cells
Scroll down to the middle of the link labeled Prevent Copy/Paste over Data Validated cells.

...write a macro that detects updates to the cells, determines if data validation is enabled on the cells, and then denies the paste if true.

1. Select all the cells that have data validation and name them DataValidationRange. You can name ranges of cells by typing the name to the left of the formula bar, where the Cell address is located.
2. In the code module for the worksheet (Sheet1, for example), add the following code:

Please Login or Register  to view this content.

Unfortunately, VBA is the only way to prevent someone from pasting over the cells. This has been an issue with Data Validation in Excel since the beginning. Hope it helps either way.

Application.Undo undoes the last action (paste). It's just like clicking the undo icon.

I don't know why it's throwing an error there.

Originally Posted by Eftychia

I named 5000 cells per column (*10 columns) with data validation list in them. Is it possible the error correlates with the number of cells?

Unless you're pasting over all 5000 cells, I wouldn't think that would be an issue. As a test, try it on a sheet with just a few DV cells.

You can use list validation where the list is on another sheet, but you need to create a named range (static or dynamic), and use that instead of referring to the range directly.

For example, to use the validation range Sheet2!A1:A10 on Sheet1, create a name with either Workbook or Sheet1 scope that refers to it (Bob Refers to: Sheet2!A1:A10), and then use that name in the DV: =Bob

Also, the code should bracket the Undo statement with Application.EnableEvents = False/True to avoid retriggering itself.

Please Login or Register  to view this content.

Hi again,

If I replace the "Application.Undo" with the :

Please Login or Register  to view this content.

then the message box appears even if I choose something on the list and not only in copy/paste.

I tried the code:

Please Login or Register  to view this content.

in a xlsm with 3 columns of data validation and it didn t work. I attach a file to see the exact error message. Just choose in a blue cell a value from the list.
Warning! Better save any open workbook before because the programm stucks...

Please take a few minutes to re-read the forum rules, and then edit your post to add CODE tags.

Thanks.

Thank you.

Untested:

Please Login or Register  to view this content.

Sorry:

Please Login or Register  to view this content.