Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro won't execute when Worksheet is Protected
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro won't execute when Worksheet is Protected
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. "MP" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro won't execute when Worksheet is Protected
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 "MP" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
execute macro on Worksheet selection | Excel Programming | |||
How to execute a macro in a protected worksheet? | Excel Programming | |||
Macro in a protected worksheet | Excel Programming | |||
How can I make macros execute on a protected sheet? | Excel Programming | |||
Record Worksheet Content as Macro and Execute from another Worksheet | Excel Programming |