Protect / Unprotect with checkbox

[retseort]

I have searched the web on this and found instructions to do this but they are unclear for someone at my skill level.

What I need help on:

I want assign code to a check box that can be used as follows:

When box is checked the spread sheet is protected.

When box is unchecked the spreadsheet is not protected

I need to know the code and how to assign it to the check box. Please remember: Phrases like '"enter this into the check box event code" does nto ehlp me.

Treat me as an idiot.

[Jim Cone]

r,

"Treat me as an idiot."...

Excel already provides a ready way to protect/unprotect a worksheet...
Tools | Protection | Protect Sheet

Jim Cone
San Francisco, USA

"retseort"
wrote in message
news:[email protected]
I have searched the web on this and found instructions to do this but
they are unclear for someone at my skill level.
What I need help on:
I want assign code to a check box that can be used as follows:
When box is checked the spread sheet is protected.
When box is unchecked the spreadsheet is not protected
I need to know the code and how to assign it to the check box. Please
remember: Phrases like '"enter this into the check box event code" does
nto help me.
Treat me as an idiot.
retseort

[retseort]

Thanks Jim.

I am aware of that. But when building a spreadsheet for a more novice user who will need to unlock and lock the spreadsheet this would be a useful function. It will also make it easier to toggle the protection as changes are made.

Anyone who can help it would be appreciated.

[retseort]

Ok I did get something to work. Using the Control toolbar I inserted a check box and altered the code as follows

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveSheet.Protect
ElseIf CheckBox1.Value = False Then
ActiveSheet.Unprotect
End If
End Sub

The issue is that once I use it it will protect and unprotect but not all of the excel capabilities will activate when the sheet is unprotected.

For example once I click on the check box as unchecked I can delete formulas in cells and so on but the formating buttons at the top of the screen will no longer work. They are greyed out. So basically once the spreadsheet is unprotected I only get partial formatting capabilities back.

[Jim Cone]

r,
Try adding the following line just above "End If"...
ActiveCell.Select
Jim Cone
San Francisco, USA


"retseort"
wrote in message
news:[email protected]
Ok I did get something to work. Using the Control toolbar I inserted a
check box and altered the code as follows

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveSheet.Protect
ElseIf CheckBox1.Value = False Then
ActiveSheet.Unprotect
End If
End Sub

The issue is that once I use it it will protect and unprotect but not
all of the excel capabilities will activate when the sheet is
unprotected.
For example once I click on the check box as unchecked I can delete
formulas in cells and so on but the formating buttons at the top of the
screen will no longer work. They are greyed out. So basically once the
spreadsheet is unprotected I only get partial formatting capabilities
back.
retseort

[retseort]

It worked perfect, thanks

Final code:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
ActiveSheet.Protect
ElseIf CheckBox1.Value = False Then
ActiveSheet.Unprotect
ActiveCell.Select
End If
End Sub