Execl 2010 - protect all sheets in one workbook

I have an excel 2010 workbook that has 12 sheets.  I want to protect each sheet (only allowing the users to "select unlocked cells").  Is there a way to protect all 12 sheets at once with one password or do I have to select each sheet to do this process?  Thanks.
mmj1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
It has to be done individually but it can be done within a Do Loop so it is the same password for each sheet.

I have done something similar, I will see if I can pull out and tweak the code.

Thanks
Rob H
Rob HensonFinance AnalystCommented:
The version I had didn't use Do Loop after all; it is only 3 sheets.

Once you have set the Protection Criteria once for each sheet, you don't need to do it again, the settings will stay each time the sheet is Protected.

Sub ProtectAll()

    Sheets("FORECAST_CURRENT").Protect Range("Password").Value
    Sheets("FORECAST PRIOR").Protect Range("Password").Value
    Sheets("EXPENSES FORECAST").Protect Range("Password").Value
    Sheets("FORECAST_CURRENT").Select

End Sub

Sub UnprotectAll()
    
    Sheets("FORECAST_CURRENT").Unprotect Range("Password").Value
    Sheets("FORECAST PRIOR").Unprotect Range("Password").Value
    Sheets("EXPENSES FORECAST").Unprotect Range("Password").Value
    Sheets("FORECAST_CURRENT").Select

End Sub

Open in new window

This uses the contents of a Range (called Password) to store the password. The range is then set using an OFFSET formula, just puts a couple of levels of "annoyance" in the way of prying eyes of people trying to get the password. The determined "hacker" can still get it no matter how well you protect.
Martin LissOlder than dirtCommented:
Sub Protekt()

Dim ws As Worksheet

For Each ws In Worksheets
    ws.Protect Password:="My Password"
Next

End Sub

Open in new window

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Martin LissOlder than dirtCommented:
You can turn my macro into a protect/unprotect toggle this way.

Sub Protekt()

Dim ws As Worksheet
Static PROTECTED As Boolean

For Each ws In Worksheets
    If PROTECTED Then
        ws.Unprotect Password:="My Password"
    Else
        ws.Protect Password:="My Password"
    End If
Next

PROTECTED = Not PROTECTED

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmj1Author Commented:
Thank you both for your input.  It has been a while since I have worked in Visual Basic so I will give this a try and as a last resort - I will just add the protection to each sheet the long way!  thanks again.
Martin LissOlder than dirtCommented:
My apologies if you already know how to add a macro but just in case...

In Excel, Press Alt+F11 to open Visual Basic Editor (VBE)

Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu

Copy the macro (you can use the ‘Select All’ button if you like) and paste it into the right-hand pane of the VBA editor ("Module1" window)

Press Alt+F11 again to go back to Excel

Optionally, press Alt+F8 to open the "Macro" dialog window. Select the macro, click ‘Options…’,  hold down the Shift key and type the letter A (or any other letter) and click ‘OK’.  Then anytime you want to run the macro press Ctrl+Shift+A
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.