Hide unhide multiple sheets

I have a workbook with a sheet named “Index” listing all 30 shees on the workbook .
On this sheet, each Row, starting on Row 6, Column B (entitled “Sheet Name”) has the sheet names, Column D (entitled “Hide ?”) has “No” for the sheets I want displayed and “Yes” for the ones I want hidden. I have 2 requests:

  1. A macro code which would hide the selected sheets
  2. A macro code to unhide all sheets
    PS: I have browsed for any previous response to a similar request and could not find a suitable answer


Appears this post does what you want → Dynamic hide/unhide sheets based on cell value

As for a reveal of all, it is a simple loop through the sheets to set all to visible.

What code have you tried already?

Many thanks, Ratslinger


Using the code from the link in my comment ( +1 there to @mauricio ) produced the following:

Option Explicit
Sub sheets
    Dim oDoc          As Object
    Dim oHomeSheet    As Object
    Dim oSheetResult  As Object
    Dim oCellD        As Object
    Dim oCellB        As Object
    Dim sSheetView    As String
    Dim sSheetName    As String
    Dim iRow          As Integer
    Dim v
    oDoc = ThisComponent
    For iRow = 5 to 34
        oHomeSheet = oDoc.Sheets.getByName("Index")
        oCellD = oHomeSheet.getCellByPosition(3, iRow)
        oCellB = oHomeSheet.getCellByPosition(1, iRow)
        sSheetView = LCase(oCellD.String)
        If sSheetView = "yes" Then
            v = False
        ElseIf sSheetView = "no" then
            v = True
            Exit Sub
        End If
        oSheetResult = oDoc.Sheets.getByName(oCellB.String)
        oSheetResult.isVisible = v
End Sub

Sub ShowAll
    Dim oDoc     As Object
    Dim iCount   As Integer
    Dim x        As Integer
    oDoc = ThisComponent.Sheets
    iCount = oDoc.Count
    For x = 0 to iCount -1
        oDoc.getByIndex(x).isVisible = True
End Sub

The sub sheets hides/reveals base upon your Column D - rows 6 to 35. If an entry is not ‘yes’ or ‘no’ the process stops.

The sub ShowAll reveals all sheets.

Thanks, excatly what I needed.