Protecting workbook sheets

[Learner101b]

Is there a way to protect multiple sheets in a workbook at the same time?

I have a workbook that contains 20 worksheets of different offices and then
I total these worksheets on a 'summary' worksheet. I modify the format of
the worksheets often and complete this task by selecting all of the tabs and
making global changes. I have the sheets protected since the different
offices enter data and my problem is that the 'unprotect sheets' option is
grayed when all of the sheets are selected. I have to individually
'unprotect' and then re-protect each sheet and I know there has to be a
better way.

Thanks for the help.

Learner101b

 

[JLatham]

Two macros to help you out:

Sub UnprotectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect
Next
End Sub

Sub ProtectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect
Next
End Sub


To get the code into your workbook, open it up and press [Alt]+[F11] to
enter the Visual Basic Editor. In it, use its menu to Insert | Module and
then copy the code above and paste it into the new module.

You can access the routines using Tools | Macro | Macros

If your sheets have a password assigned, you'll have to code that in also,
like this:
Sub UnprotectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect password:="mypassword"
Next
End Sub

Sub ProtectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect password:="mypassword"
Next
End Sub

If you have special protection options set and want to make sure things are
set that way when you use the ProtectAllSheets macro, record a macro of your
own while protecting one the way you want, and copy and modify the line of
code that it generates into the ProtectAllSheets macro, replacing the
one-liner currently in it. You'll still have to add the
password:="mypassword" (replace mypassword with your real one).

For example, recording a macro might provide this line of code:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
you'd need to change that to
anySheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
and if you have a password, then:
anySheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
password:="mypassword"

Hope this helps.

 

[Learner101b]

Thanks very much for your help. I am only semi-technical, but I can
certainly do what you told me to do. I even understand your macro although I
have not used them before.

Any idea why Microsoft doesn't make this easier? I think a lot of people
would want this capability and all Microsoft would need to do is 'un-gray'
the option. Am I missing something.

Thanks again,
Learner101b

Two macros to help you out:

Sub UnprotectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect
Next
End Sub

Sub ProtectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect
Next
End Sub


To get the code into your workbook, open it up and press [Alt]+[F11] to
enter the Visual Basic Editor. In it, use its menu to Insert | Module and
then copy the code above and paste it into the new module.

You can access the routines using Tools | Macro | Macros

If your sheets have a password assigned, you'll have to code that in also,
like this:
Sub UnprotectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect password:="mypassword"
Next
End Sub

Sub ProtectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect password:="mypassword"
Next
End Sub

If you have special protection options set and want to make sure things are
set that way when you use the ProtectAllSheets macro, record a macro of your
own while protecting one the way you want, and copy and modify the line of
code that it generates into the ProtectAllSheets macro, replacing the
one-liner currently in it. You'll still have to add the
password:="mypassword" (replace mypassword with your real one).

For example, recording a macro might provide this line of code:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
you'd need to change that to
anySheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
and if you have a password, then:
anySheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
password:="mypassword"

Hope this helps.

Is there a way to protect multiple sheets in a workbook at the same time?

I have a workbook that contains 20 worksheets of different offices and then
I total these worksheets on a 'summary' worksheet. I modify the format of
the worksheets often and complete this task by selecting all of the tabs and
making global changes. I have the sheets protected since the different
offices enter data and my problem is that the 'unprotect sheets' option is
grayed when all of the sheets are selected. I have to individually
'unprotect' and then re-protect each sheet and I know there has to be a
better way.

Thanks for the help.

Learner101b

 

[JLatham]

I think the most likely reason you aren't allowed to do it to more than one
sheet at a time is that:
#1 - all sheets may not be protected with the same password,
#2 - some sheets needing to be protected may need different setups
#3 - they wanted to see if anyone could figure out what we have with it.

You can further modify the code to work with any workbook at all. Instead
of referring to ThisWorkbook. in the 2 macros, use ActiveWorkbook. Then
when you have any workbook open, open the one with those macros in it, go
back to the other workbook (one with sheets to work with) and run the macro
from that workbook's Tools | Macro | Macros menu.

Thanks very much for your help. I am only semi-technical, but I can
certainly do what you told me to do. I even understand your macro although I
have not used them before.

Any idea why Microsoft doesn't make this easier? I think a lot of people
would want this capability and all Microsoft would need to do is 'un-gray'
the option. Am I missing something.

Thanks again,
Learner101b

Two macros to help you out:

Sub UnprotectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect
Next
End Sub

Sub ProtectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect
Next
End Sub


To get the code into your workbook, open it up and press [Alt]+[F11] to
enter the Visual Basic Editor. In it, use its menu to Insert | Module and
then copy the code above and paste it into the new module.

You can access the routines using Tools | Macro | Macros

If your sheets have a password assigned, you'll have to code that in also,
like this:
Sub UnprotectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Unprotect password:="mypassword"
Next
End Sub

Sub ProtectAllSheets()
Dim anySheet As Worksheet

For Each anySheet In ThisWorkbook.Worksheets
anySheet.Protect password:="mypassword"
Next
End Sub

If you have special protection options set and want to make sure things are
set that way when you use the ProtectAllSheets macro, record a macro of your
own while protecting one the way you want, and copy and modify the line of
code that it generates into the ProtectAllSheets macro, replacing the
one-liner currently in it. You'll still have to add the
password:="mypassword" (replace mypassword with your real one).

For example, recording a macro might provide this line of code:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
you'd need to change that to
anySheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
and if you have a password, then:
anySheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
password:="mypassword"

Hope this helps.

Is there a way to protect multiple sheets in a workbook at the same time?

I have a workbook that contains 20 worksheets of different offices and then
I total these worksheets on a 'summary' worksheet. I modify the format of
the worksheets often and complete this task by selecting all of the tabs and
making global changes. I have the sheets protected since the different
offices enter data and my problem is that the 'unprotect sheets' option is
grayed when all of the sheets are selected. I have to individually
'unprotect' and then re-protect each sheet and I know there has to be a
better way.

Thanks for the help.

Learner101b