Home / Protection / How to Lock and Protect all Worksheets in a Workbook

How to Lock and Protect all Worksheets in a Workbook

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

About Excel Help Desk

Check Also

How to Unlock and UnProtect all Worksheets in a Workbook

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.

11 comments

  1. I would like to be able to Lock all worksheets but…. both be able to “select unlocked cells” and to “select locked cells” as well. The locked cells are still protected, but they are able to be selected for copying.

    • Anita,
      Can you send us a copy of the workbook that you are working on with this….please send to support@excelhelpdesk.com
      Include the password for the worksheets and any further description of what you want to acheive
      Regards
      ExcelHelpDesk Support Team

  2. I am trying to unlock multiple pages in a workbook and when I go to run the following code it gives me a Compile Error message “Invalid Outside Procedure”. What am I doing wrong?

    Sub Unprotectc()
    Dim wsWorksheet As Worksheet

    For Each wsWorksheet In ActiveWorkbook.Worksheets
    wsWorksheet.Protect Password:=”Nautiquel”
    Next
    End Sub

  3. Just what I was looking for – – thanks for the tip!! I was wondering if it would be possible to execute this macro every time the workbook is closed. This way, if I unprotect the worksheet and forget to re-protect it, the macro would take care of it when I close the workbook.

    • John,
      It is possible to include a call to this macro before a workbook is closed. We would recommend including the call within the BeforeClose event for the workbook you need to ensure the protection is set for

      Private Sub Workbook_BeforeClose(Cancel As Boolean)
       
          Call LockWorksheets
       
      End Sub
  4. This worked in excel 2010, but it does not prompt me for a password when I protect or unprotect all. It does prompt for password when unprotecting one sheet at a time using the built in button. I would rather not use a password. I was just curious why it does not prompt for the password when protecting or unprotecting all. Thank you.

    • Thanks for the question….
      Can I check some things when you say using the built in button do you mean the button available on the Excel 2010 ribbon ?
      If that is the case then it will ask you for the password each time as you have previously locked all the worksheets with a password. If you used one of the macros above then it would have set the password to be “password”.
      To Lock all worksheets without a password simply leave out the password parameter.
      Note if you had previously locked worksheets with a password you will need to clear the password first

    • Hi Sherrie
      If you want the prompt to ask you for a password use the following.

      Sub LockWorksheets()

      Dim strPassword As String
      Dim wsWorksheet As Worksheet

      strPassword = InputBox(“Enter a password to protect all worksheets.”, “Protect Sheets”)

      If strPassword = “” Then
      MsgBox “No password entered. Goodbye”, vbInformation, “No Password”
      Exit Sub
      End If

      For Each wsWorksheet In ActiveWorkbook.Worksheets
      wsWorksheet.Protect Password:=strPassword

      Next

      End Sub

      • Sorry try this with the correct vba formatting and new lines.

        Sub LockWorksheets()
         
        Dim strPassword As String
        Dim wsWorksheet As Worksheet
         
        'Ask for new password
        strPassword = InputBox(“Enter a password to protect all worksheets.”, “Protect Sheets”)
         
        'If password is not entered or cancel button clicked then exit.
        If strPassword = “” Then
        MsgBox “No password entered. Goodbye”, vbInformation, “No Password”
        Exit Sub
        End If
         
        For Each wsWorksheet In ActiveWorkbook.Worksheets
        wsWorksheet.Protect Password:=strPassword
         
        Next
         
        End Sub