• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying/pasting vales can bypass data validation. Is there a way to prevent this?

I'm thinking could it be that you can write some VB code so that anytime somebody pastes values in a cell or range of cells, have Excel run data validation for those cells if those cells contain data validation?
 
Interesting observation.

have Excel run data validation for those cells if those cells contain data validation?

But that would be after the fact. DV applies to new data entry, not to existing cell values.

And implementing the "data validation" with VBA code to perform the checks defeats the purpose of having a DV feature in Excel in the first, IMHO.

-----
PS.... I suppose MSFT might justify the current behavior by arguing that DV tests data entry, whereas paste-value pastes values.

It is not the same as "data entry".

It is a subtle distinction.

To demonstate, in a new worksheet with no DVs, enter the formula =1+2.22E-16 into A1, copy A1, and paste-value back into A1.

Note that =A1-1=0 returns FALSE because the value in A1 is not exactly 1.

Now, copy A1 and paste into C1. Note that =C1-1=0 also returns FALSE.

So, we can paste values that have more than 15 significant digit of precision, but we cannot enter 1.000000000000000222 (15 zeros) as data.
 
Hello DN,

Based on your thread title:-

Data validation is a method used to restrict user input to what is in the DV list. Hence, if you're just trying to prevent Users, for whatever reason, from pasting over data validated cells, then place the following code into the worksheet module:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
         MsgBox "Sorry, but you can't paste into data validated cells.", vbExclamation, "WARNING"
         Application.CutCopyMode = False
    End If

End Sub

Change the range to suit your needs. The User will be able to paste anywhere outside the required range but not into the data validated range.
Please note that this is not totally fool proof as anyone with some form of VBA knowledge could over- ride this. However, when confronted with a pop-up message, a User will usually back off.

If the message becomes annoying (it will appear with every click on a data validated cell), just remove it from the code above.

I hope that this helps.

Cheerio,
vcoolio.
 
Last edited:
Back
Top