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
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
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
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.