Application.Undo Error

[jsahmed]

I am attempting to write a function that does not allow users to copy text into a forced data validation. For some reason I am receiving an Application.Undo error? Can anyone provide guidance? Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
" It would have deleted data validation rules. Please select from the dropdown menu.", 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

 

[JoeMo]

You need to disable events before the undo so that the undo doesn't trigger the change event code again. Try:

Else
with Application
.EnableEvents = False
.Undo
.EnableEvents = True
End with

 

[jsahmed]

Thanks for your help JoMoe. For some reason I am still receiving a debugging error with the .Undo

My apologies I have never used this function before


Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox "Your last operation was canceled." & _
" It would have deleted data validation rules. Please select from the dropdown menu.", vbCritical
End If
End Sub

 

[JoeMo]

I cannot reproduce that error so can't tell you why you are getting it. One point to note is that your function HasValidation will return True only if EVERY CELL in the range r has IDENTICAL validation. Might be better to apply it to just the target range like this:

If HasValidation(Range(Target)) Then
Exit Sub
......