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?

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

--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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?

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" wrote:

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

--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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?

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?
--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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" wrote:

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

--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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?

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" wrote:

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?
--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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" wrote:

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

--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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?

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
--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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" wrote:

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?
--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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" wrote:

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

--
HTH...

Jim Thomlinson


"NikkiBenz" wrote:

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?