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.
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.
Hi, I am trying to restrict copy pasting of data on to cells having data validation. I have multiple columns in an excel sheet having data validation.
I have found out a vba code which can help me in this, but that one code works for one column only. Since I have multiple columns with data validation, I want to update the same vba code which can work all the columns with data validation.
Can you suggest how to edit the code.
Interociter Operator commented
I COMPLETELY AGREE ! YES I AM SHOUTING!!!
In the mean time, Frequently go to DATA, Data Validation menu dropdown, and select Circle Invalid Data. Then you have to scan the sheet for the red circles.
I nearly blew the Interociter up because I forgot that step!
In my opinion, pasting should not wipe out validation for the cell. That makes creating a validated template pretty much pointless. I also find it annoying that formatting is carried over when pasting although understand that in some cases that is what you want. It would be nice if there was a "paste and match style" like many other applications on Mac have. This would allow you to easily paste without formatting.
@dee that's all good and efficient however you can't limit a user to only use"match destination formatting " therefore sort of invalidates the use of validation data check
Use the "Match Destination Formatting" Paste options, it will retain the Data Validation criteria.
There is no way to prevent this other than using VBA to identify changed cells. Best case is allowing users to paste to non-validated cells and refuse changes to pre-determined values.