Spreadsheets: Protecting All Worksheets

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

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.

 

Discuss

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