Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting workbook sheets
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting workbook sheets
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting workbook sheets
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 "JLatham" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protecting workbook sheets
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. "Learner101b" wrote: 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 "JLatham" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting sheets within a workbook | Excel Discussion (Misc queries) | |||
Password protecting sheets in a workbook | Excel Worksheet Functions | |||
Help w/ protecting all sheets | Excel Discussion (Misc queries) | |||
Protecting Sheets | Excel Discussion (Misc queries) | |||
Protecting sheets | Excel Discussion (Misc queries) |