Excel files containing multiple worksheets that are all password protected can be time-consuming to work with unless you know this great piece of VBA script that can unprotect, or protect, all Excel worksheets in one action, including an option to deal with hidden worksheets. This post will show you how to protect and unprotect all Excel worksheets with a single click.
Excel Protection
Excel worksheet protection offers numerous benefits for Excel developers. However, a common challenge arises when dealing with a file containing multiple worksheets that are all password protected.
While safeguarding these worksheets from alterations is beneficial, it can create hurdles during redevelopment, file enhancement, and during crucial stages like testing or error handling. Now, imagine managing a file with over 20 worksheets where you’re required to protect and unprotect each sheet individually. This can be incredibly time-consuming, particularly considering the need to enter the password twice every time you protect a worksheet.
Fortunately, there’s a more efficient solution to unprotect all Excel worksheets, streamlining the entire process and saving valuable time.
VBA Script
The VBA scripts offer a robust solution for managing worksheet protection in Excel. The ‘ProtectAllSheets’ subroutine not only sets a password for each worksheet but also allows for the specification of various protection options, such as enabling users to use AutoFilter or sort data while the sheet is protected. This adds a layer of flexibility, allowing you to tailor the protection features to your specific needs.
Similarly, the ‘UnprotectAllSheets’ subroutine provides a way to efficiently remove protection from all worksheets in a workbook, with the option to include or exclude hidden sheets. Both scripts prompt for a password, adding a level of security and customization.
These scripts can be integrated into your existing VBA projects, enabling automated protection or unprotection as part of larger processes. For instance, you can unprotect sheets, execute certain tasks (like updating data), and then reapply protection, all within a single script. This makes the scripts highly versatile, suitable for both standalone use in development work and as part of more complex automated workflows.
It’s important to note that the effectiveness of these scripts assumes a consistent password across all worksheets. While they can handle different scenarios, such as whether to include hidden sheets, the assumption of a uniform password remains key for seamless operation.
Protect All Worksheets VBA
To protect all Excel worksheets using a password then the following VBA Script can be used, you are prompted to find out if you want to include protecting any hidden sheets
Sub ProtectAllSheets()
Dim sht As Worksheet
Dim password As String
Dim protectHiddenSheets As Boolean
Dim response As VbMsgBoxResult
' Ask the user if they want to protect hidden sheets
response = MsgBox("Do you want to protect hidden sheets?", vbYesNoCancel)
If response = vbCancel Then Exit Sub
protectHiddenSheets = (response = vbYes)
' Prompt the user to enter a password
password = InputBox("Enter the password to protect all sheets:", "Password Input")
' Exit if no password is entered
If password = "" Then Exit Sub
On Error GoTo ErrorHandler
For Each sht In ActiveWorkbook.Worksheets
If protectHiddenSheets Or sht.Visible = xlSheetVisible Then
sht.Protect Password:=password
End If
Next sht
MsgBox "All sheets protected successfully!", vbInformation
Exit Sub
ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub
Unprotect All Worksheets VBA
To unprotect all Excel worksheets we make a minor change to the script:
Sub UnprotectAllSheets()
Dim sht As Worksheet
Dim password As String
Dim unprotectHiddenSheets As Boolean
Dim response As VbMsgBoxResult
' Ask the user if they want to unprotect hidden sheets
response = MsgBox("Do you want to unprotect hidden sheets?", vbYesNoCancel)
If response = vbCancel Then Exit Sub
unprotectHiddenSheets = (response = vbYes)
' Prompt the user to enter the password
password = InputBox("Enter the password to unprotect all sheets:", "Password Input")
' Exit if no password is entered
If password = "" Then Exit Sub
On Error GoTo ErrorHandler
For Each sht In ActiveWorkbook.Worksheets
If unprotectHiddenSheets Or sht.Visible = xlSheetVisible Then
sht.Unprotect Password:=password
End If
Next sht
MsgBox "All sheets unprotected successfully!", vbInformation
Exit Sub
ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub
Additional Protection Options
There are occasions when you want to set the options for the worksheet protection from within the script. For example you might want to specify that the users can select unlocked cells or a common one is that users can still use the Autofilter option. To specify any additional options you can toggle them between True and False in the VBA below:
Sub ProtectAllSheets()
Dim sht As Worksheet
Dim password As String
Dim protectHiddenSheets As Boolean
Dim response As VbMsgBoxResult
' Ask the user if they want to protect hidden sheets
response = MsgBox("Do you want to protect hidden sheets?", vbYesNoCancel)
If response = vbCancel Then Exit Sub
protectHiddenSheets = (response = vbYes)
' Prompt the user to enter a password
password = InputBox("Enter the password to protect all sheets:", "Password Input")
' Exit if no password is entered
If password = "" Then Exit Sub
On Error GoTo ErrorHandler
For Each sht In ActiveWorkbook.Worksheets
If protectHiddenSheets Or sht.Visible = xlSheetVisible Then
' Protect sheet with additional options
sht.Protect Password:=password, UserInterfaceOnly:=True, _
AllowFiltering:=True, AllowUsingPivotTables:=True, _
AllowInsertingRows:=False, AllowDeletingRows:=False, _
AllowInsertingColumns:=False, AllowDeletingColumns:=False, _
AllowSorting:=True, AllowFormattingCells:=False, _
AllowFormattingColumns:=False, AllowFormattingRows:=False
End If
Next sht
MsgBox "All sheets protected successfully!", vbInformation
Exit Sub
ErrorHandler:
MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub
This great bit of code has saved me a lot of time in the past and I know it will continue to save much more in the future. I hope that now you are also able to protect and unprotect all Excel worksheets with a simple click the process and code proves as useful for you,
Keep Excelling,
Now that you have learned all about how to protect and unprotect all Excel worksheets take your Excel skills to the next level. Check out our latest guide on how to create a drop down list in Excel and enhance those Excel Dashboard building skills today!
