Excel Macro Protect Unprotect All Worksheets

Protecting/unprotecting multiple worksheets within a workbook is tedious. Here is a macro for doing either for all the sheets at one time within a workbook.

If you have a workbook open that contains one macro, that macro will be available to other open workbooks or worksheets if macros are enabled in that workbook.

I have copied the information from here in case his site goes south.

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

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.