Protect and Unprotect All Worksheets at Once

Add to Favorites

This will allow you to protect or unprotct all worksheets in a workbook at once. It doesn't allow you to input a password; however, if you run the unprotect_all macro and there are worksheets that are password protected, it will prompt you to input the password in order to unprotect the worksheet and will do so for every worksheet that is password protected.

Where to install the macro:  Module

Protect All Worksheets at Once

Sub Protect_All()

For i = 1 To Sheets.Count
 Sheets(i).Protect
Next i

 End Sub

Unprotect All Worksheets at Once

Sub Unprotect_All()

For i = 1 To Sheets.Count
 Sheets(i).Unprotect
Next i

End Sub





Similar Content on TeachExcel
Print All Worksheets at Once in Excel
Tutorial: How to print all worksheets at once from Excel. This saves you the time of having to go to...
View Different Parts of a Worksheet at Once in Excel
Tutorial: View different sections of the same worksheet when the sections are far apart.  This allo...
Display all Formulas at Once in Excel
Tutorial: How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsh...
3D References - Formulas that Reference Multiple Sheets at Once in Excel
Tutorial: Have one simple formula that will reference the same cell or range of cells on multiple w...
Edit Multiple Worksheets at Once in Excel
Tutorial: How to edit multiple worksheets at the same time; this includes adding, editing, deleting,...
View Separate Parts of a Worksheet at Once in Excel
Tutorial: View separate parts of a large worksheet while in the same Excel window. This allows you t...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Print All Worksheets at Once in Excel
Tutorial: How to print all worksheets at once from Excel. This saves you the time of having to go to...
View Different Parts of a Worksheet at Once in Excel
Tutorial: View different sections of the same worksheet when the sections are far apart.  This allo...
Display all Formulas at Once in Excel
Tutorial: How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsh...
Excel Forum