Archive of Mr Excel Message Board
Back to Controls in Excel archive index
Back to archive home
checkbox to protect / unprotect a worksheet
Posted by frank godlewski on September 12, 2001 10:38 AM
beginning a novice at macros, i tried to use the
macro recorder to do the following:
create a checkbox, that when it is check,
the workshet is protected.
when it is unchecked, the worksheet is unprotected.
i have seen example spreadsheets that use this,
but have no idea how to add this type of control
to a worksheet.
Re: checkbox to protect / unprotect a worksheet
Posted by Cory on September 12, 2001 11:27 AM
Try adding this to your checkbox's code event:
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveSheet.Protect
ElseIf CheckBox1.Value = False Then
ActiveSheet.Unprotect
End If
End Sub
Cory
Re: checkbox to protect / unprotect a worksheet
Posted by Frank Godlewski on September 13, 2001 7:14 AM
I get the following error when clicking the checkbox:
Run-time error '1004'
Unable to get the CheckBoxes property of
the Worksheet class.
What's the fix?
It may be this...
Posted by Cory on September 13, 2001 10:27 AM
I tried recreating the error, but was unable to. When I first tried using sheet protection through code, I encountered this error, but it was because my sheet was already protected and I was trying to protect it again. However, when I tried this code on a sheet of my own, I had no problems (whether it was already protected or not). Is this the only control and code in your workbook? Are there other things going on while this code is trying to be run?
Cory
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.