Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I unprotect a protected worksheet that has passwrod protect | Excel Discussion (Misc queries) | |||
how do I protect a worksheet and still use a checkbox control | Excel Worksheet Functions | |||
How to protect and unprotect 30 worksheets in a file every month . | Excel Worksheet Functions | |||
Is there any way that you can protect or unprotect a group of wor. | Excel Discussion (Misc queries) | |||
How to protect my macro | Excel Discussion (Misc queries) |