Unlock/Lock all sheets Excel

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 
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

 

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

 


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.