1. There's going to be a serious problem with:
Code:
Application.EnableEvents = False
'Does the validation range still have validation?
If HasValidation(Range("E2:F32")) Then
Exit Sub
Else
because set EnableEvents to False, if the range has intact data validation, the Exit sub gets executed while Enable events is still false, so you have no cxhance of catchimng subsequent data validation infractions.
2. I was unable to duplicate your scenarion unless I started out with a data validation already missing from that range.
3. It may serve you to check that the cell(s) that have changed include cell(s) in the in the data validation range that you want to protect.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Set xxx = Intersect(Target, Range("E2:F32"))
If Not xxx Is Nothing Then
'Does the validation range still have validation?
If HasValidation(Range("E2:F32")) Then
Exit Sub
Else
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Your last operation was cancelled. It would have deleted data validation rules.", vbCritical
End If
End If
End Sub
If there is more than one data validation range you want to protect then a tweak is required.
Bookmarks