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