Are there ways to protect multiple sheets at once?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Monty - 29 May 2008 19:53 GMT

I have created a report for our company that covers each day of the month - a
31 page workbook. It looks great, but I now need to protect it. I realize
that I can protect it one worksheet at a time, but this is a bit tedious. I
also know that I can protect the entire workbook from having pages deleated.
What I need to do is protect each page individually, using the same password,
so that none of the formulas are lost. Is there a way to do all 31 sheets at
once or do I have to each of the individually? Thanks.

Reply to this Message

Sandy Mann - 29 May 2008 20:10 GMT

You could do it with a Macro. Something like:

Sub LockIt()
For Each sh In Sheets
sh.Protect password:="Secret"
Next sh
End Sub

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>I have created a report for our company that covers each day of the month -
>a
[quoted text clipped - 8 lines]
> at
> once or do I have to each of the individually? Thanks.

Reply to this Message

Mike H - 29 May 2008 20:41 GMT

Hi,

I'm afraid you have to resort to VB but it's not complicated.
Alt+F11 to open VB editor
right click 'This Workbook' and insert module
Paste these 2 Subs in.

To run then from your worksheet
Tools|Macro|Macros
Select 'This Workbook'
Highlight the macro name and click run

Sub marine()
For x = 1 To Worksheets.Count
Worksheets(x).protect Password:="Mypass"
Next
End Sub

Sub marine1()
For x = 1 To Worksheets.Count
Worksheets(x).Unprotect Password:="Mypass"
Next
End Sub

Mike

> I have created a report for our company that covers each day of the month - a
> 31 page workbook. It looks great, but I now need to protect it. I realize
[quoted text clipped - 3 lines]
> so that none of the formulas are lost. Is there a way to do all 31 sheets at
> once or do I have to each of the individually? Thanks.

Reply to this Message

Gord Dibben - 29 May 2008 23:57 GMT

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Unprotect Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Gord Dibben MS Excel MVP

>I have created a report for our company that covers each day of the month - a
>31 page workbook. It looks great, but I now need to protect it. I realize
[quoted text clipped - 3 lines]
>so that none of the formulas are lost. Is there a way to do all 31 sheets at
>once or do I have to each of the individually? Thanks.

Reply to this Message