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.
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.
Saul Cruz commented
no updates on this yet, would be nice to have this feature
as Ted Says "f the author took the time to manually create Data Validation on a worksheet, users shouldn’t be able to just paste over it or delete it from the worksheet."!!!!!!
Raja Lakshman commented
Hope you all can vote for that.
Ted Tyler commented
Problem: There is no good solution to prevent users from bypassing (or removing) the Data Validation process (both intentionally and unintentionally). Users are able to paste over cells that contain Data Validation and completely remove it from the worksheet. Some users have no idea when this occurs and others do this intentionally because they want to use their own data and formatting. Users are also able to paste their data as values directly into the cell with Data Validation and bypass the entire process. Protecting the worksheet doesn’t work because you still need to allow the user to input data into the cell.
The majority of Excel users are not technical in nature and most corporations no longer allow macros or Visual Basic, we must rely on the skills of the author and the capabilities of the software to reduce improper use and notify the user when "invalid data" is entered.
Simple Solution: In the Settings Tab, there are 2 checkboxes: Ignore blank and In-cell dropdown. Adding the following options gives the author more control over the data being entered by the user.
☑ Prevent overwrite – This should probably be included by default. If the author took the time to manually create Data Validation on a worksheet, users shouldn’t be able to just paste over it or delete it from the worksheet.
☑ Allow paste values/Circle invalid data – There is value in allowing users to paste values into cells containing Data Validation. This option allows users to paste their data as values into cells with Data Validation but it also circles each cell that does not meet the Data Validation criteria. If this box is not checked, users must be restricted from pasting values into the cell but not restricted from pasting into the formula bar, double clicking inside the cell, or by selecting the cell and pressing the F2 key.
Pavan Tumu commented
I have the exact same problem, Data validation is defined on a cell in excel to pull values from a DB. When some one types into the cell validation works fine, although if some one copy data from a different excel or source into the sheet that has data validations defined then the validations wont fire.
Kenneth Barber commented
I'm guessing that data validation counts as formatting. By default, pasting and filling overwrite formatting.
Try protecting your sheet.