How to Unlock and UnProtect all Worksheets in a Workbook

Jan 12th 2011, Category: Protection, Tags: , ,

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

Related posts:

  1. How to Lock and Protect all Worksheets in a Workbook
  2. Unhide Multiple Worksheets
  3. How to print Worksheets to different Printers

Discussion

9 responses to "How to Unlock and UnProtect all Worksheets in a Workbook"

  • Bob Coleman says:

    Amazing – this is the exact code I needed in simply language with good instructions that I needed. You saved me about 2000 plus left clicks/rights clicks and a lot of time. I changed Unprotect to Protect to reverse the process and I could reverse the process after I made changes.

  • Tracy says:

    I did like that, but it shows: “The macros in this project are disabled…”. Can you help me with other way?
    Thank u so much if you can send me e-mail: thuhuong2301@yahoo.com
    Best regards,

    • Excel Help Desk says:

      Tracey,
      When opening the workbook with the macro’s you will see a security warning in the top left and a button to enable content or review options. Simply enable the macros to use this functionality.
      If you are still having problems you can send through a copy of you workbook for us to review – support@excelhelpdesk.com
      Regards
      ExcelHelpDesk

  • Tina Spicer says:

    My worksheet doesn’t have a password.

    I tried the above code, but when I try to run it, I get this message:

    Compile Error:
    Expected: End of statement

    Sub Unprotect_All() For i = 1 To Sheets.Count Sheets(i).Unprotect Next i End Sub

    What am I doing wrong?

    Thanks for the help.

  • Biko says:

    I’ve got a problem when i try to unprotect all sheets in my workbook with this code.

    When i activate this macro with a button, it asks the password i’ve set.
    when i fill in the password, it only unprotects one sheet, and ask again for a password for the next sheet.

    How can i unprotect all my sheets, without filling the password in for about 20 times?

    Tanks for the help!

  • kellycm says:

    I’d like to unprotect the first 10 sheets of my 25+ workbook. how can i do this?

    • Excel Help Desk says:

      Kelly,
      You should be able to modify the VBA script included to this post to acheive that
      If you need assistance send through a copy of your workbook including the password that is used to lock all the worksheets and we can provide back a version of this to unlock only the first 10 worksheets. Send the workbook to support@excelhelpdesk.com
      Regards
      ExcelHelpDesk Support Team

Leave a Comment

Wrap code blocks with <pre lang="VB" > code goes here </pre> to appear with VBA formatting.



SUBSCRIBE WITHOUT COMENTING