Protecting/Unprotecting Worksheets within click event handlers


Posted by Jean on July 03, 2001 2:59 PM

On a protected worksheet, the following click event handler code produces an "Unprotect method of Worksheet class failed" message when the code is executed by pressing the associated command button.

If the code is stepped into directly (bypassing the command button), the worksheet is unprotected as expected. Behavior is similar when attempting to Protect unprotected worksheets from a command button, or if the Protect/Unprotect commands are embedded within the called routine (e.g AssignInductorsToBins).

Public Sub AssignInductorsToBinsButton_Click()
' Worksheets("Inductor Sheet").Activate
ActiveSheet.Unprotect
Run ("AssignInductorsToBins")
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Any observations/suggestions on this unexpected command button behavior would be appreciated!
Thanks,
Jean

Posted by Ivan F Moala on July 03, 2001 7:16 PM


Jean.....Excel97.....right click on the commandbutton
Select Properties then select TakeFocusonclick
and set this to False. Apparently the sheets
cell(s) has to be activated for the command
to initiate, hence the Take focus on click.
Selecting a worksheet range will also work.


Ivan



Posted by Jean on July 05, 2001 2:01 PM


THANKS Ivan. Great advice!!
Jean