How to Unlock and UnProtect all Worksheets in a Workbook

10
583

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