Archive of Mr Excel Message Board


Back to Controls in Excel archive index
Back to archive home

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

Re: code for check box

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


Re: code for check box

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



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.