Hide/Unhide Worksheet Array?

[thelostscott]

Hi all,

I'm having trouble writing the code to hide & unhide an array of worksheets by clicking a "Button" (Macro assigned shape).

I though it would work with something like:

Sub Hide_Unhide_Workings()
Application.ScreenUpdating = False
 
If ActiveWorkbook.Sheets(Array("Download", "Pivot", "Download 2", "Download 3", "#WORKINGS#")).Visible = True Then
 
ActiveWorkbook.Sheets(Array("Download", "Pivot", "Download 2", "Download 3", "#WORKINGS#")).Visible = xlVeryHidden
    Else
        ActiveWorkbook.Sheets(Array("Download", "Pivot", "Download 2", "Download 3", "#WORKINGS#")).Visible = True
 
ActiveWorkbook.Sheets("Download 2").Activate
ActiveWorkbook.Sheets("Download 2").Range("E2").Select
 
End If
 
Application.ScreenUpdating = True
End Sub

But that freezes up when it tries to identify the array as visible.

Any ideas?

 

[GlennUK]

I believe that making sheets visible can only be done one sheet at a time ( as it is in the Unhide dialog ).

 

[thelostscott]

So would I have to go through each sheet in the array I specified above and do something like this:

Sub Hide_Unhide_BW_Workings()
Application.ScreenUpdating = False
 
If ActiveWorkbook.Sheets("Download").Visible = True Then
        ActiveWorkbook.Sheets("Download").Visible = xlVeryHidden
    Else
        ActiveWorkbook.Sheets("Download").Visible = True
End If
 
If ActiveWorkbook.Sheets("Download 2").Visible = True Then
        ActiveWorkbook.Sheets("Download 2").Visible = xlVeryHidden
    Else
        ActiveWorkbook.Sheets("Download 2").Visible = True
End If
 
Application.ScreenUpdating = True
End Sub

 

[GlennUK]

You can loop, like this:

        myarray = Array("Download", "Pivot", "Download 2", "Download 3", "#WORKINGS#")
        For Each el In myarray
            If Sheets(el).Visible = True Then
                Sheets(el).Visible = xlVeryHidden
            Else
                Sheets(el).Visible = True
            End If
        Next