Excel – Hide/UnHide WorkSheet(s)

A couple more simple Excel VBA procedures that might be useful to some.

It can be very useful to hide certain worksheets from the users, for instance, sheet used for lookups, lists,… and procedure such as the ones presented below can help you automate the task of hiding, as well as restoring them for you administer them.

Hide/UnHide all the Worksheets

Hide All The WorkSheets

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_HideAll
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Hide all the worksheets except for the active sheet
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/

' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25             Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_HideAll()
    On Error GoTo Error_Handler
    Dim WrkSht                As Excel.Worksheet

    Application.ScreenUpdating = False
    For Each WrkSht In Worksheets
        If WrkSht.Name <> ActiveSheet.Name Then WrkSht.Visible = xlSheetVeryHidden
    Next WrkSht

Error_Handler_Exit:
    On Error Resume Next
    Application.ScreenUpdating = True
    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_HideAll" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

UnHide All The WorkSheets

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_UnhideAll
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Loop through all the WorkSheets of the current WorkBook and set them all
'             to visible.
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/

' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25                 Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_UnhideAll()
    On Error GoTo Error_Handler
    Dim WrkSht                As Excel.Worksheet

    Application.ScreenUpdating = False
    For Each WrkSht In Worksheets
        WrkSht.Visible = xlSheetVisible
    Next WrkSht

Error_Handler_Exit:
    On Error Resume Next
    Application.ScreenUpdating = True
    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_UnhideAll" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Hide/UnHide an Individual Worksheet

Hide A Single WorkSheet

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_Hide
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Hide the specified worksheet
'               Note:  You cannot hide the current 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 hide
'
' Usage:
' ~~~~~~
' Call WrkSht_Hide(WorkSheets("Sheet2"))
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25             Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_Hide(WrkSht As Excel.Worksheet)
    On Error GoTo Error_Handler

    If WrkSht.Name <> ActiveSheet.Name Then WrkSht.Visible = xlSheetVeryHidden

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_Hide" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

UnHide A Single WorkSheet

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_Unhide
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : UnHide/Show 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 UnHide/Show
'
' Usage:
' ~~~~~~
' Call WrkSht_Unhide(WorkSheets("Sheet2"))
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25             Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_Unhide(WrkSht As Excel.Worksheet)
    On Error GoTo Error_Handler

    WrkSht.Visible = xlSheetVisible

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_Unhide" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub