cfo.com

Print this article | Return to Article | Return to CFO.com

Spreadsheets: Protecting All Worksheets

Use a few simple macros to easily guard your worksheets instead of locking them up one by one.
Bill Jelen, CFO.com | US
December 29, 2010

Editor's Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com's Spreadsheet Tips Page.

Reader Ebrima S. wins an e-book of Learn Excel 97-2007 from CFO and MrExcel for his question: "I frequently develop Excel templates with a number of sheets. However, I protect the sheets, one by one. I want to find out if there is any fast method to protect multiple sheets at the same time."

There are a few annoying tasks that cannot be accomplished across all worksheets in a workbook. For example, for some reason you cannot specify that rows 1:3 should print at the top of every worksheet when you are in Group mode. Another annoying limitation is that you cannot password protect all of the worksheets at once. Even if you are willing to use the same password on all worksheets, Excel will not let you protect the sheets in Group mode.

Both of these tasks can be dramatically simplified with a tiny bit of VBA (Visual Basic for Applications) code in the workbook. Even if you have never used macros, this article will walk you through adding the simple macro to your workbook.

 

If You've Never Run Macros Before
By default, Excel is set to prevent macros from running. You will have to use the following steps once to allow macros to run.

• In Excel 2010:
Choose File, Options, Trust Center, Trust Center Settings, Macro Settings, Disable All Macros with Notification

•In Excel 2007:
Choose Office Button, Excel Options, Trust Center, Trust Center Settings, Macro Settings, Disable All Macros with Notification

• In Excel 2003 and earlier:
Choose Tools, Macro, Security, Medium

Avoid the .XLSX File Type in Excel 2007/2010
Microsoft invented a new macro-free file type starting in Excel 2007, and then made the new file type the default in Excel 2007 and Excel 2010. If your template is currently saved with an .XLSX extension, you cannot save macros in the file. In that case, do a Save As, and change the file type to .XLSB or .XLSM. Close the file and then reopen to allow macros.

Switch from Excel to the VBA Editor
Every Windows version of Excel since 1993 includes a powerful VBA development environment lurking behind the worksheets. You can use VBA code to add new functionality to Excel. To get to the VBA environment, follow these steps:

1. Type Alt+F11

2. From the VBA menu, select Insert, Module

3. Press Ctrl+R to display the Project Explorer

You should now see a blank white canvas where you can enter VBA code on the right and a list of worksheets and your new module on the left, as shown in Figure 1.

Fig. 1

17MrExcelFig01

 

Paste the Macro
Copy and Paste the following lines into the VBA editor:

Sub ProtectAllSheets()

    For Each ws In ActiveWorkbook.Worksheets

        ws.Protect Password:="secret123"

    Next ws

    MsgBox "All Worksheets Protected"

End Sub

Sub UnProtectAllSheets()

    For Each ws In ActiveWorkbook.Worksheets

        ws.Unprotect Password:="secret123"

    Next ws

    MsgBox "All Worksheets Unlocked"

End Sub

At the two spots indicated in Figure 2, type the real password that you would like to use. The password in both macros should match.

Type Alt+Q to close the VBA editor and return to Microsoft Excel.

Fig. 2

17MrExcelFig02

 

Running the Macro
As shown in Figure 3, type Alt+F8 to display the list of macros in the workbook. Choose ProtectAllSheets and click the Run button. The macro will loop through every worksheet in the active workbook and protect the worksheet with the password specified in the code.

Fig. 3

17MrExcelFig03

 

Later, if you need to work on the template and want to unprotect all worksheets, use Alt+F8 and run the UnprotectAllSheets macro.

Good Enough for Novice to Intermediate Excellers
First, I should point out that no password is truly secure in Excel. Anyone can use Google and a few minutes of searching to find out how to easily crack Excel passwords. The passwords used in this quick solution are particularly insecure. Anyone who can find his way to the VBA window will be able to discover the password. If the people using your worksheet can find their way to the VBA, you might want to modify the macros to prompt you for the password:


Sub ProtectAllSheets()

    Ans = InputBox(Prompt:="Enter Password", Title:="Protect All")

    For Each ws In ActiveWorkbook.Worksheets

        ws.Protect Password:=Ans

    Next ws

    MsgBox "All Worksheets Protected"

End Sub

Sub UnProtectAllSheets()

    Ans = InputBox(Prompt:="Enter Password", Title:="Unprotect All")

    For Each ws In ActiveWorkbook.Worksheets

        ws.Unprotect Password:=Ans

    Next ws

    MsgBox "All Worksheets Unlocked"

                                                               End Sub

This modification will require you to type the password once each time you want to protect or unprotect all worksheets.

Bill Jelen is a CFO contributing editor and author of 32 books about Excel, including LiveLessons Excel Macros and VBA. You can win a copy of one of his books if your question is selected for a column. Post your question to the CFO Community Center on the right.

 




CFO Publishing Corporation 2009. All rights reserved.