PDA

View Full Version : [SOLVED] Unprotect and protect after macro run



Hobb3s
07-06-2005, 10:18 AM
Hello,
I'm attempting to create a user friendly worksheet for our staff. However I'm running into a problem I hope someone can help me with. I have a macro assigned to a check box that bolds the indicated line when the box is checked and removes the bold when the box is not checked. However I require the sheet to be protected to keep the users from deleting rows or columns, so that my totals that are linked in other workbooks aren't compromised.
When the sheet is protected I get an erroor 1004, when I attempt to use the checkboxes with the assigned macro.
Someone pointed out that it is possible to turn off and on protection within the macro. I was wondering how this is done.
Here is what I have so far that I wish to add the protection off and on to.


Sub CheckBox4_Click()
If Range("L4").Value = True Then
Range("A4:H4").Select
Selection.Font.Bold = True
Else
If Range("L4").Value = False Then
Range("A4:H4").Select
Selection.Font.Bold = False
End If
End If
End Sub


Thanks for any help you can give me.
Cheers,
Greg.

Bob Phillips
07-06-2005, 10:55 AM
Presumably, you have the checkbox linked to cell L4? That is a problem, as the error will occur when you check the box.

What sort of checkbox is it, Forms or Control Toiolbox (I am assuming the former). If it Forms, unlink it, and use this code



Sub CheckBox4_Click()
With ActiveSheet
.Unprotect
Range("A4:H4").Font.Bold = .CheckBoxes(Application.Caller).Value = xlOn
.Protect
End With
End Sub

austenr
07-06-2005, 10:55 AM
Hi welcome to the board. You can use this at the beginning of the macro:


ActiveSheet.Unprotect

and at the very end:


ActiveSheet.Protect

Hobb3s
07-06-2005, 12:30 PM
thank you for your quick responses,
I went with the second solution, because I need the FALSE to be entered into the L4 cell so that it translates over via the link in another document to tell that line how to behave (in the other document).
Thanks again!

austenr
07-06-2005, 12:33 PM
If your problem is solved, please mark your thread solved. You can do that via Thread Tools right above your first post.

Hobb3s
07-06-2005, 12:37 PM
thanks for the tip.

jwilder1
07-07-2005, 01:27 AM
You can also add password protection by entering

.unprotect("1234") with 1234 as password(or any value you choose)

Jim

austenr
07-07-2005, 06:50 AM
Good tip. Didn't know that. Thanks :thumb