I recently had a client who has a requirement to protect and unprotect a lot of sheets. This was something they didn’t do very often, but when they did, they described the process of doing it manually as cumbersome.
I’m sure that once you get more than a few sheets, protecting and unprotecting each one by hand does indeed become a chore.
So I wrote a couple of macros that does the job for you. One macro protects all the sheets in a workbook, the other unprotects them all.
I just use a For ... Next loop to go through each sheet in turn. Then I use either the Protect or Unprotect method on each worksheet object.
For Each WSheet In Worksheets WSheet.Protect Password:=Pwd Next WSheet
The password that is stored in the string variable Pwd is input by the user. So the password isn't stored in the VBA code itself, making it secure and therefore allowing you to distribute the workbook with the VBA included if you wish.
In order to get the password, the code uses an InputBox to ask the user to enter it before protecting/unprotecting sheets.
Pwd = InputBox("Enter the password to protect all worksheets", "Enter Password")
If a blank password is entered the code displays a message saying this and then exits the macro without doing anything.
How to Use The Code
As this is something that you can use on multiple workbooks, I'd create a Personal.xlsb (if you don't already have one), and copy/paste the code in there.
By default everything on the sheet is protected which includes things like shapes, charts and macros. The insertion/deletion of rows and columns is not allowed, neither are formatting changes.
Sorting, filtering and the use of pivot tables are also not allowed on a protected sheet. As you may want your users to be able to do these things, you can alter the macro to allow this.
To do this we specify the relevant parameters for the Protect method like this:
WSheet.Protect Password:=Pwd, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
The default values for AllowSorting, AllowFiltering and AllowUsingPivotTables are False, so we don't need to explicitly specify them if we want to pevent these particular actions.
Follow this link to check the MSDN reference for the Worksheet.Protect method
Password Protected Ranges
If you have protected all sheets, and also have ranges protected with different passwords, your users can still edit these ranges by entering the correct password(s) for those ranges.
Read this article to see how to protect different ranges with different passwords.
Leave a Comment or Please Share This
I'd love to hear your feedback on this code (or your tips for the World Cup), so please leave a comment here.
Enter your email address below to download the sample workbook.
Option Explicit Sub ProtectAllSheets() ' Written by Philip Treacy ' My Online Training Hub https://www.myonlinetraininghub.com/protect-and-unprotect-all-sheets-in-a-workbook ' June 2014 Dim WSheet As Worksheet Dim Pwd As String Application.ScreenUpdating = False Pwd = InputBox("Enter the password to protect all worksheets", "Enter Password") If Pwd = vbNullString Then NoPassword End If For Each WSheet In Worksheets WSheet.Protect Password:=Pwd Next WSheet Application.ScreenUpdating = True End Sub Sub UnProtectAllSheets() ' Written by Philip Treacy ' My Online Training Hub https://www.myonlinetraininghub.com/protect-and-unprotect-all-sheets-in-a-workbook ' June 2014 Dim WSheet As Worksheet Dim Pwd As String Application.ScreenUpdating = False Pwd = InputBox("Enter the password to unprotect all worksheets", "Enter Password") If Pwd = vbNullString Then NoPassword End If On Error Resume Next For Each WSheet In Worksheets WSheet.Unprotect Password:=Pwd Next WSheet If Err <> 0 Then MsgBox "The password you entered is incorrect. All worksheets are still protected.", vbCritical, "Incorrect Password" End If On Error GoTo 0 Application.ScreenUpdating = True End Sub Sub NoPassword() MsgBox "You didn't enter a password. This macro will not continue.", vbCritical + vbOKOnly, "No password entered." Application.ScreenUpdating = True End End Sub