Macro won't execute when Worksheet is Protected

[MP]

I have a worksheet with the following macro

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Not Intersect(Target, Me.Range("B8")) Is Nothing Then
Target.Offset(3, 0).ClearContents
Target.Offset(4, 0).ClearContents
Target.Offset(5, 0).ClearContents
Target.Offset(6, 0).ClearContents
Target.Offset(12, 0).ClearContents
Target.Offset(16, 0).ClearContents
ErrHandler:
Application.EnableEvents = True
End If
End Sub

It works fine until I protect the worksheet, then it stops working? I tried
putting a digital signature on the macro and then accepted the digital
signature when starting the worksheet and that still does not work. Is there
something else I need to do when I protect? The cells that I am clearing are
not locked.

Thanks in advance
MP

 

[JLGWhiz]

Probably need to use the Worksheets(#).Unprotect.Password("Password") at the
beginning of the macro and then reset Protect at the end of the macro.

 

[JLatham]

It works for me under the setup you describe in Excel 2003. However, I
notice that you never diabled event handling, which is going to cause the
routine to be called repeatedly for each .ClearContents. To reduce that
overhead, I rewrote it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B8")) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Offset(3, 0).ClearContents
Target.Offset(4, 0).ClearContents
Target.Offset(5, 0).ClearContents
Target.Offset(6, 0).ClearContents
Target.Offset(12, 0).ClearContents
Target.Offset(16, 0).ClearContents
ErrHandler:
Application.EnableEvents = True
On Error GoTo 0 ' reset trap
End If
End Sub