Hello All,

I need to prevent copy pasting the data that does not conform to data validation rules in the cells of the excel. I have tried various approaches mentioned in other threads, but of no use. It is leading to infinite loop of messages saying "Your last operation was canceled It would have deleted data validation rules"
Can somebody help me out in solving this issue of mine. Below is the code. Please suggest necessary changes.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim shSource7 As Worksheet
Dim rngHeadings As Range
Dim result As Boolean
Dim oCell As Range


Set shSource7 = Worksheets("Data")

Set rngHeadings = shSource7.Range("A2:K99")

If HasValidation(rngHeadings) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
Exit Sub
End If

End Sub


Private Function HasValidation(r) As Boolean

On Error Resume Next
X = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function