Protect/Unprotecting Sheets with CmdButtons

[NikkiBenz]

My workbook has 14 worksheets (4 of them are data sheets and are hidden to
the user). Most of the sheets have command buttons to open userForms to
capture data and run reports.

I want to protect all the sheets to prevent the user from messing with the
formulas, etc. In all of the userform_Initialization events, I am running a
UnprotectSheets module and a ProtectSheets module on UserForm_Terminate
events.

The problem is that I'm getting 1004 Runtime errors when trying to
protect/unprotect certain sheets (does not happen on just one specific
sheet). When I try to protect/unprotect manally on the sheet by going to
Tools/Protection... the protect/unprotect option is greyed out. It becomes
available when I click somewhere in the worksheet and then I can proceed with
the manual way or even the macro way. I think it has something to do with
what's on focus in the sheet. I think the sheet loses the focus when the
button is clicked. (If that makes sense)

So, I thought I could be clever by selecting or activating a cell (range) in
each worksheet before protecting/unprotecting, but I still get the runtime
error. The sheets are password protected. I'm accessing the password via a
constant field. But I know it's not a problem with the password.

Sub ProtectSheets()
Dim ws As Worksheet
For each ws in ActiveWorkbook.Worksheets
ws.Range("A1").Select
ws.Protect constPassword
Next ws
End Sub

The unprotectSheets looks the same other than ws.Unprotect constPassword

Do you have any suggestions?

 

[Jim Thomlinson]

The one thing I notice is you are trying to select on a sheet that is not the
active sheet which will not work...

Try this...

Sub ProtectSheets()
Dim ws As Worksheet
For each ws in ActiveWorkbook.Worksheets
ws.Protect constPassword
Next ws
End Sub

 

[NikkiBenz]

Thanks! I tried that too and I'm still having the same problem. I need to
somehow get the focus on the worksheet and not on the cmdButton (for each
sheet, not just the sheet I clicked the button in)

 

[Jim Thomlinson]

I am not able to recreate your problem here. Can you post the code you are
using to initialize the form and to terminate the form. There should be no
need to select the sheet. You indicated in your original post that the
protect button is grayed out. Should I assume then that your forms are
non-modal 9allowing you to access the sheets while the form is up?

 

[NikkiBenz]

Actually, a way to recreate the problem is this:
In a worksheet, add a commandButton (you don't have to rename it or assign
a macro to it, do anything to it)
Make sure you are out of "Design Mode".
Click the button. (It will be "In Focus")
Click Tools, Protection
Notice the "Protect Sheet" function is greyed out.

I can't select "Protect Sheet" until I click somewhere in the sheet to get
the focus off of the command button. That's where my macros are failing.
The ProtectSheet option is "greyed out" and not available because the focus
is on a button.

So, I'm trying to get the focus off of the button and on to the sheet in
order to protect/unprotect them.
Does that make sense?

 

[Jim Thomlinson]

Yes but if you put this code in the commad button it works just fine, which
is why I think there is something else going on (Command button from the
Control Toolbox, not Forms toolbar).

Private Sub CommandButton1_Click()
Dim wks As Worksheet

For Each wks In Worksheets
wks.Protect
Next wks
End Sub

 

[NikkiBenz]

I failed to mention I'm using Excel 97 (don't get me started on my company
using this version)

I took your code and put it in Excel 2003 (on my home computer) and it
worked like a charm! HOWEVER, Excel 97 sings to a different song. I'm
still getting errors with the 97 version. Grrr! I'm at a loss for ideas!

 

[Jim Thomlinson]

Now that is a whole new ball game...

You can try this... Untested since I don't have 97

Sub TryThis
dim wks as worksheet
dim wksStart as worksheet

set wksStart = activesheet
for each wks in worksheets
wks.select
wks.range("A1").Select
wks.protect
next wks
wksStart.select
end sub

 

[NikkiBenz]

I think you and I were testing/thinking of this solution at the same time. I
just figured it out on my end and it is exactly what you typed below! Your
original messaged helped me out with saying you can't select a cell in a
range if the worksheet is not active. The lightbulb FINALLY came on!!!!
Thank you SO MUCH for your help!

 

[Tom Ogilvy]

You could have avoided that by changing the TakefocusOnClick property of the
commandbutton to false. In xl97, you should just about always set the
takefocusonclick property of the commandbutton to false to avoid problems.

However, in my experience it is still go to make the worksheet active when
you are protecting it. I have seen some weird behavior when you don't

 

[NikkiBenz]

Ahhh...good to know. Thank you!

You could have avoided that by changing the TakefocusOnClick property of the
commandbutton to false. In xl97, you should just about always set the
takefocusonclick property of the commandbutton to false to avoid problems.

However, in my experience it is still go to make the worksheet active when
you are protecting it. I have seen some weird behavior when you don't