How to Lock and Protect all Worksheets in a Workbook

11
781

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 apply Worksheet Protection to single Worksheet, some Worksheets or all Worksheets in a Workbook. In future posts we will provide an macro that will Disable Worksheet Protection.

 

Option 1 – Protect 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 Worksheet Protection will be enabled for all worksheets in that Workbook

Sub LockWorksheets()
'This routine will go through and Protect 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.Protect Password:="password"
Next

End Sub

Option 2 – Protect a Specify Worksheet

You can modify the code above to only include Protection on a specific Worksheet by including the following code to the Macro.

Sub LockWorksheets()
'This routine will go through and Protect all the worksheets for the workbook

Dim wsWorksheet As Worksheet

For Each wsWorksheet In ActiveWorkbook.Worksheets
If wsWorksheet.Name = "Sheet1" Then
wsWorksheet.Protect Password:="sheet1"
Else
wsWorksheet.Protect 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