Stop Copy & Paste overwriting Data Validation
When you have data validation on a cell, a drop down list for example. A user can circumvent all of this by either copying and pasting into the cell or when dragging from other cells numbers can increment and therefore render the data validation, invalid. A user should not be able to copy and paste values which are not in the validation.
Jim White commented
SpiffyDog ... your instructions do not work (for me, in Office 365 ProPlus). Locking a cell with a DV list does allow me to select the drop-down still, but as soon as I select an entry in the list then I get an error (cell is protected …).
This obviously makes sense … the cell is protected which means you cannot change its value by any means.
1. Create the data validation list (dropdown) for the cell(s).
2. Make sure that the protection is set to 'Locked'.
3. Lock the worksheet.
The user can still use the dropdown on the cell(s) but cannot paste to it.
Seriously need the feature to prevent copy-paste or dragging down that overwrite whatever data validation that has in place. It is creating too much hassle and time consuming to correct these inputs manually when you have a big data set.
Agree with the sentiment below
Agree with the sentiment below, its needed yesterday.
Yes this is stupid, Excel should change it immediately
agreeing with other comments, this is a rudimentary feature that makes excel validation very week.
We receive thousands of emails from our vendors using our data validated template. Half of vendors bypass validation via pasting wrong data. Macro files are not allowed.
This feature was needed years ago
Most data gets into excel via copy/paste. If data validation doesn't work in this scenario, it's mostly ineffective.
I think this is an essential fix.
I was really hoping to find the answer to this problem, I guess I'll just up-vote this
RIGHT CLICK DROP DOWN CANT NOT BE ABLE TO PASTE ON VALIDATE FILED.
Need this. ASAP.
Felipe Sozinho commented
Yes, please implement this. Otherwise templates become obsolete, a lots of re-work are needed. Christian comment is a great option!!
I have a slightly different take on this. On protected sheets locked cells should have an option to allow values to be typed in or pasted but an option to deny data validation rules, formatting, or conditional formatting to be overwritten with a paste.
Looks like nothing has been done since 5 years ago...
This is much needed- I spend too many hours each week dealing with data templates!
Ideally there would be a configurable option for how the rules are applied (maybe this could be per validation rule rather than per workbook?)
1- Allow paste into Data Validated cells to override rules (this is currently what happens)
2- Allow paste into Data Validated cells but block paste and notify user for any values that are not accepted values.
3-Allow paste into Data Validated cells and allow even unacceptable values but notify the user and circle these.
James Goodhew commented
Agreed with all of the above. Please get this done.
As the author of the workbook I am supposed to know what is required in the column. Simply allowing users to accidentally paste that work out of existence.
Allow them to paste, but the paste has to pass the validation.
I spend lot of time setting up data validation. Sometimes it will change the reference list. It lost the reference sheet, but remain the reference column.
Hi, I have an excel sheet, where in I have multiple columns with data validation. I want to restrict the copy pasting of data in the cells having data validations. For this, I have found one vba code. But, this one code works with only one column. Just want to know, do we have an universal vba code, which works for all the columns in the sheet having data validation.