Ask Your Question
0

Hide unhide multiple sheets

asked 2020-01-22 04:46:19 +0100

Mauriciogfj gravatar image

updated 2020-08-03 12:37:21 +0100

Alex Kemp gravatar image

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 Thanks Muricio PS: I have browsed for any previous response to a similar request and could not find a suitable answer

edit retag flag offensive close merge delete

Comments

Hello,

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?

Ratslinger gravatar imageRatslinger ( 2020-01-23 03:50:03 +0100 )edit

Many thanks, Ratslinger

Mauriciogfj gravatar imageMauriciogfj ( 2020-01-28 01:06:10 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-01-23 06:13:52 +0100

Ratslinger gravatar image

updated 2020-01-23 06:15:17 +0100

Hello,

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
        Else
            Exit Sub
        End If
        oSheetResult = oDoc.Sheets.getByName(oCellB.String)
        oSheetResult.isVisible = v
    Next
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
    Next
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.

edit flag offensive delete link more

Comments

Thanks, excatly what I needed.

Mauriciogfj gravatar imageMauriciogfj ( 2020-01-28 01:07:05 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-01-22 04:46:19 +0100

Seen: 1,089 times

Last updated: Jan 23