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.

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

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?

Posted by Cory on September 13, 2001 10:27 AM

It may be this...

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