Worksheet_Change stopped working after sheet is protected

[pat12]

Hello world

I am still a newbie with VBA and it took me quite a long time to build
(with your help ofcourse) code presented below

------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'Does the validation range still have validation?

If HasValidation(Range("E2:E1001")) = False Or
HasValidation(Range("F2:F1001")) = False Or
HasValidation(Range("U2:U1001")) = False _
Or HasValidation(Range("AJ2:AJ1001")) = False Or
HasValidation(Range("AN2:AN1001")) = False Or Range("check") > 0 Then

Application.Undo

MsgBox "Operation canceled. It would destroy validation data",
vbCritical '

Else

Exit Sub
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 worked very well (filed check is sum of column in which 0 means data
in range are ok, 1 are not ok) and I was very satisfied for a moment
but then I put protection on sheet and the code just stopped working. I
have no errors. It just let me destroy validation data format without
msg box. When I unprotect sheet it is working propely again.

I am very confused

Any help appreciated
PAT

 

[pat12]

JLGWhiz napisal(a):

Did you attempt to save the changes you made with the protection on? You
will probably find that you cannot.

I did. As I remember I saved the changes. If you have spare time you
can try to my code to new sheet and test. Everything looks fine except
the code is not working.

I also noticed that sometimes when I tried to copy the code to new
sheet it becomes unstable. Macro was doing something till Ctrl+Break
was hit. (on unprotected sheet) and eventually I cut off the code
although I think the idea of the code is very good (I don't know other
protection for validation data in the cell)

Regards
PAT