I have this code that does not allow users to paste over cells with data validation
It works great until I protect the sheet and have these cells unlocked, it allows any value to be pasted into the cells.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("J10:J1999")) Then
Exit Sub
Else
Application.Undo
MsgBox "Please enter a valid date.", vbCritical
End If
End Sub
Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
It works great until I protect the sheet and have these cells unlocked, it allows any value to be pasted into the cells.