VBA – Hide worksheets when not activate

Posted on November 8, 2014 by

If you are building a worksheet with some VBA in it and you are willing to have only one sheet visible in a time, then you need a small piece of code, hiding all other sheets.

What we should do? Simply a for-each loop which goes through all the sheets and checks their name. If the name is different from the name of the current active sheet, you simply need to hide it.

There it goes:

Sub <span class="hiddenSpellError">HideWorksheets</span>()

    Dim <span class="hiddenSpellError">ws</span> As Worksheet
    Dim <span class="hiddenSpellError">sName</span> As String

    <span class="hiddenSpellError">sName</span> = ThisWorkbook.ActiveSheet.Name
    
    For Each <span class="hiddenSpellError">ws</span> In ThisWorkbook.Worksheets
        If <span class="hiddenSpellError">ws</span>.Name <> <span class="hiddenSpellError">sName</span> Then ws.Visible = xlSheetHidden
    Next ws

End Sub

Sub HideWorksheets()

Dim ws As Worksheet

Dim sName As String

sName = ThisWorkbook.ActiveSheet.Name

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> sName Then ws.Visible = xlSheetHidden

Next ws

End Sub

🙂

Tagged with: hide worksheets, show only active worksheet, VBA

VBA – Hide worksheets when not activate

Related posts: