As I mentioned, in my last post, I’ve been doing Excel development recently, so I thought I’d post a few procedures, to spread the wealth as they say. Most of these I created quite a few years ago for other projects that I revamped for this newest project.

This post is about Protecting and Unprotecting a worksheet or all the Worksheets in a workbook.

Protect/UnProtect All the Worksheets

Below are 2 procedures that loop through all the worksheets within a workbook and Protect/UnProtect them all using the same password.

Protect All The WorkSheets

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_ProtectAll
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loop through all the workbook's worksheets and protect them all
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPwd      String - Password to protect the worksheets with
'
' Usage:
' ~~~~~~
' Call WrkSht_ProtectAll("YourPwd")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25             Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_ProtectAll(sPwd As String)
    On Error GoTo Error_Handler
    Dim WrkSht                As Excel.Worksheet

    For Each WrkSht In ActiveWorkbook.Worksheets
        WrkSht.Protect sPwd
    Next WrkSht

Error_Handler_Exit:
    On Error Resume Next
    If Not WrkSht Is Nothing Then Set WrkSht = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WrkSht_ProtectAll" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

UnProtect All The WorkSheets

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_UnProtectAll
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loop through all the workbook's worksheets and unprotect them all
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPwd      String - Password to unprotect the worksheets with
'
' Usage:
' ~~~~~~
' Call WrkSht_UnProtectAll("YourPwd")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25             Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_UnProtectAll(sPwd As String)
    On Error GoTo Error_Handler
    Dim WrkSht                As Excel.Worksheet

    For Each WrkSht In ActiveWorkbook.Worksheets
        WrkSht.Unprotect sPwd
    Next WrkSht

Error_Handler_Exit:
    On Error Resume Next
    If Not WrkSht Is Nothing Then Set WrkSht = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WrkSht_UnProtectAll" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

 

Protect/UnProtect an Individual Worksheet

Below are 2 procedures that Protect/UnProtect the specified worksheet.

Protect A Single WorkSheet

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_Protect
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Protect the specified worksheet
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' WrkSht    Object - Worksheet to protect
' sPwd      String - Password to protect the worksheets with
'
' Usage:
' ~~~~~~
' Call WrkSht_Protect(WorkSheets("Sheet2"), "YourPwd")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25             Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_Protect(WrkSht As Excel.Worksheet, sPwd As String)
    On Error GoTo Error_Handler

    WrkSht.Protect sPwd

Error_Handler_Exit:
    On Error Resume Next
    If Not WrkSht Is Nothing Then Set WrkSht = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WrkSht_Protect" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

UnProtect A Single WorkSheet

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_UnProtect
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Unprotect the specified worksheet
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' WrkSht    Object - Worksheet to unprotect
' sPwd      String - Password to unprotect the worksheets with
'
' Usage:
' ~~~~~~
' Call WrkSht_UnProtect(WorkSheets("Sheet2"), "YourPwd")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25             Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_UnProtect(WrkSht As Excel.Worksheet, sPwd As String)
    On Error GoTo Error_Handler

    WrkSht.Unprotect sPwd

Error_Handler_Exit:
    On Error Resume Next
    If Not WrkSht Is Nothing Then Set WrkSht = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WrkSht_UnProtect" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub