Try using my example in a different module to set up the page, so something like
Sub ProtectSheet()
Dim ws As Worksheet
Dim UsableRng As Range
Set ws = Worksheets("Sheet1")
Set UsableRng = ws.Range("A1:XFD1")
UsableRng.Locked = False
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Once that is done you should be able to run your original code ie:
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.", 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
as long as the range that you provided is the same as the range that the user's will be able to access.
Bookmarks