How to protect or unprotect all Excel worksheets in a file using VBA

An Excel file 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…

 

Excel Protection 
Excel worksheet protection has many advantages to the Excel developer but one of the challenges that every Excel developer will come across at some point is the file containing multiple worksheets that are all password protected.

Worksheet protection is a simple but very effective feature that allows the Excel developer to prevent unauthorised changes to their file, you can learn more about how to implement worksheet protection here: http://dedicatedexcel.com/protect-cells-and-formulas-in-ms-excel-2010/

While protecting the worksheets from change has a clear advantage it also leads to an issue when it comes to redevelopment work, enhancing the file and testing or error handling. Imagine if you have a file with 20+ worksheets and you need to keep protecting and unprotecting each individual sheet, it adds up to a lot of time especially when you consider protecting the worksheet requires you to enter the password twice, however there is a better way!

VBA Script
The VBA script is simple; we are basically going to write a small piece of code that tells Excel to Password Protect or unprotect every worksheet within that file. This will only work if all your worksheets have the same password but that should be standard practice unless you have a business reason for using a range of different ones.
Equally this script can be inserted into your existing code, for example maybe you want to Unprotect every worksheet, apply some changes, then protect every worksheet as part of a single script or it can be used as a stand-alone piece of code for development work.

Protect All Worksheets VBA
To protect all Excel worksheets using a password then the following VBA Script can be used. The password must be within the quotation marks and is case-sensitive. If you are copying this code into your own VBA then remember to change the password:

 

1
2
3
4
5
Sub Protect()
For Each Sht In ActiveWorkbook.Worksheets
Sht.Protect Password:="temp"
Next Sht
End Sub

Unprotect All Worksheets VBA
To unprotect all Excel worksheets we make a minor change to the script as below, again remember to change the password if you decide to copy and paste this code into your own work:

 

1
2
3
4
5
Sub Unprotect()
For Each Sht In ActiveWorkbook.Worksheets
Sht.Unprotect Password:="temp"
Next Sht
End Sub

 
Additional Protection Options
There are occasions where you need 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 need to state them after the password like follows:

 

1
2
3
4
5
Sub Protect()
For Each Sht In ActiveWorkbook.Worksheets
Sht.Protect Password:="temp", AllowFiltering:=True
Next Sht
End Sub

 

This great bit of code has saved me many dreary hours in the past of typing in password after password and I know it will continue to save me a few more in the future, I hope that you find a great use for it in your own Excel work.
Keep Excelling,

Leave a Comment