Worksheet Protection with caviot

[Guest]

I have protected my workbook so users can only modify certain cells. I have
a Button to clear all cells, but when I protect the workbook, the button is
protected too. How do I unprotect this button? Currently, if the workbook
is protected, the button gives a runtime error which says the workbook needs
to be unprotected.

 

[Rick Rothstein \(MVP - VB\)]

I don't think it is the button that is "protected"; rather, it is the
protected cells you are trying to clear when you press the button that is
your problem. Look up the Protect Method in the VBA help files... I think
you will have to execute an Activesheet.Protect statement at the beginning
of the macro assigned to the button and and Activesheet.Unprotect statemen
at the end of the macro assigned to the button, setting and resetting the
applicable arguments for this method at each location.

Rick

 

[Guest]

Thanks Rick for responding. I don't know how to look up the Protect Method
in the VBA help files. In fact I don't know where/what the VBA help files
are. Is there a link you can send me to? or would you happen to know what
the statement is I need to put in my macro?

My macro reads as follows:

Range( _

"I4:I6,I9,I11,I13,I15,I17,I19,I21,I23,B25,B27,B29,D25,D27,D29,F25,F27,F29,H25,H27,H29,J25,J27" _
).Select
Range("J27").Activate
Selection.ClearContents
Range("I4:I6").Select

What would the

 

[Rick Rothstein \(MVP - VB\)]

It's a little hard to give you the statement to use because it depends on
what options you have checked off in the Tools/ProtectSheet dialog box. Each
option that you can check off has an equivalent argument that can be set to
True or False. If you only have the first two items check, they are
defaulted to True in the Protect and Unprotect methods which would mean all
you have to do is add ActiveSheet.Unprotect as the first line in your macro
and ActiveSheet.Protect as the last line in your macro. However, if you have
any other items checked, then you will need to handle them via those
method's arguments. You can see the help files on this arguments by going
into the VBA editor (where your macro code is), type the word "protect" (or
"unprotect") without the quote marks in the Immediate window, or in the code
window (don't forget to delete it from here afterwards), and then press F1
(while your cursor is next to or within the word).

Rick