Data validation, prevent paste values

[henrik2h]

I asked a similar question about formatting where I don't want the user to be able to change the formatting through copy (from elsewhere) and paste. No one seemed to have an answer.

I have the same problem with Data validation, could I somehow prevent users from copy and paste values that destroys the data validation. For instance I have one validation that says only negative numbers but it is still positive to copy another cell and paste positive numbers.

 

[JoeMo]

I don't think you can prevent this with Data Validation alone. Can be done using VBA, but then the user must have macros enabled.

 

[grizz]

assuming your drop down is in A1 this will prevent paste in it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub​