The protection of key worksheets and information within in an Excel workbook can be very important to ensure the data integrity of a spreadsheet. When developing that spreadsheet it can become very tedious to enable and disable Worksheet Protection or any other form of Excel protection.
In this post we provide a macro that can be used to automatically remove Worksheet Protection to single Worksheet, some Worksheets or all Worksheets in a Workbook. For this VBA procedure to work you will need to know the password that has been allocated to the worksheets. If you have used our Protect Worksheet post to protect your worksheets then the procedure below can be used without modification to Unprotect all worksheets.
Option 1 – Unprotect all Worksheets
Open a workbook and start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert > Module. Copy the following code into the new Module then select Run and all worksheets will be Unprotected in that Workbook
Sub UnlockWorksheets() 'This routine will go through and Unprotect all the worksheets for the workbook 'Change the Password to any required value or leave blank Dim wsWorksheet As Worksheet For Each wsWorksheet In ActiveWorkbook.Worksheets wsWorksheet.Unprotect Password:="password" Next End Sub
Option 2 – Unprotect a Specify Worksheet
You can modify the code above to only Unprotect a specific Worksheet by including the following code to the Macro.
Sub UnlockWorksheets() 'This routine will go through and Unprotect all the worksheets for the workbook Dim wsWorksheet As Worksheet For Each wsWorksheet In ActiveWorkbook.Worksheets If wsWorksheet.Name = "Sheet1" Then wsWorksheet.Unprotect Password:="sheet1" Else wsWorksheet.Unprotect Password:="password" End If Next End Sub
Note: Worksheet Protection is only enforced within those cells of a Worksheet that have been Locked. In a future post we will demonstrate how Cell Locking works and how you can use it to develop robust spreadsheet solutions.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment