Protect / Unprotect with checkbox

R

retseort

I have searched the web on this and found instructions to do this bu
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. Pleas
remember: Phrases like '"enter this into the check box event code" doe
nto ehlp me.

Treat me as an idiot
 
J

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
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
 
R

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.
 
R

retseort

Ok I did get something to work. Using the Control toolbar I inserted
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 no
all of the excel capabilities will activate when the sheet i
unprotected.

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

Jim Cone

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


"retseort"
wrote in message
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
 
R

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 Su
 
Top