Worksheet_Change stoped 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

 

[Corey]

you may need to unlock the ranges the code is refering to for the code to
run

 

[pat12]

Corey napisal(a):

you may need to unlock the ranges the code is refering to for the code to
run
news:[email protected]...

I tried. I unlock everything in the sheet. I put
UserInterfaceOnly:=True to protect sheet and nothing. If sheet is
protected the vba just does'nt work. When I unprotect it it works.

????

Help please

 

[NickHK]

You need to unlock some cells so the user is able to change some values and
trigger the _Change event.
Or you mean the event is firing, but the code errors ?

Also, do you not mean :
If HasValidation(Range("E2:E1001")) = True Or ...
instead of False ?

NickHK

 

[pat12]

NickHK napisal(a):

You need to unlock some cells so the user is able to change some values and
trigger the _Change event.
Or you mean the event is firing, but the code errors ?

Also, do you not mean :
If HasValidation(Range("E2:E1001")) = True Or ...
instead of False ?

NickHK

I did. Like I said before when sheet is unprotected the code is working
perfectly. If I try to paste to cell from range and that would damage
the validation data it does Undo and inform me with msgbox. When I
protect the sheet it just stop working. No errors , nothing I just
paste data and destroy validation rules form cell without warning. I
tried unlock all cels from sheet then protect sheets and it gives me
nothing. From some reason protection just turn off the code.

PAT

 

[pat12]

Would someone be so kind and make a test for me please

Please create new workbook and in new sheet set validation data for
list i.e. 1;2;3 for cells E2:E10. Unlock the cells E2:E10. Then put
following the code in sheet

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

If HasValidation(Range("E2:E10")) = False Then


Application.Undo

MsgBox "Operation Canceled", vbCritical '

Else

Exit Sub
End If

End Sub



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
---------------------

After that try to copy (on unprotected sheet) value form blank cell A1
and paste it in E2. It should be blocked with message operation
canceled. If that works try to do the same with sheet protected. It it
works only me have the problem. If not there is something bigger

Thanks in advance
PAT