Protect / Unprotect with checkbox
 

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.


--
retseort
------------------------------------------------------------------------
retseort's Profile: http://www.excelforum.com/member.php...o&userid=24690
View this thread: http://www.excelforum.com/showthread...hreadid=473607

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

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
------------------------------------------------------------------------
retseort's Profile: http://www.excelforum.com/member.php...o&userid=24690
View this thread: http://www.excelforum.com/showthread...hreadid=473607

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's Profile: http://www.excelforum.com/member.php...o&userid=24690
View this thread: http://www.excelforum.com/showthread...hreadid=473607

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

Protect / Unprotect with checkbox
 

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


--
retseort
------------------------------------------------------------------------
retseort's Profile: http://www.excelforum.com/member.php...o&userid=24690
View this thread: http://www.excelforum.com/showthread...hreadid=473607