Spreadsheets: Protecting All Worksheets

Use a few simple macros to easily guard your worksheets instead of locking them up one by one.

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.


Your email address will not be published. Required fields are marked *