code for check box

Posted by Paul on October 18, 2001 1:41 PM

What would be the code for using a Marco, for a check box to protect when checked and unprotect when not checked for sheet1. Excel 97. Thanks

Posted by Barrie Davidson on October 19, 2001 7:09 AM

Try,

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Sheets("Sheet1").Protect
Else
Sheets("Sheet1").Unprotect
End If
End Sub

Note, you can't have the checkbox in Sheet1 because it creates a conflict.

Hope this helps you out.
Barrie
Barrie Davidson

Posted by Martin Peters on October 19, 2001 8:55 AM

This code should work with the check box on active sheet :-

Private Sub CheckBox1_Click()
If Application.ExecuteExcel4Macro("get.document(7)") = True Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If
End Sub

In addition, to cover the possibility of the protection status being changed via the menu command rather than by the check box :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.ExecuteExcel4Macro("get.document(7)") = True Then
ActiveSheet.Unprotect
CheckBox1.Value = True
ActiveSheet.Protect
Else
CheckBox1.Value = False
End If
End Sub