VBA - hide all but one sheet including charts

[LORDMARKS]

Hi all (office 97)

I have used this site for years, but this is my first post, as I can't get any of the older posts to work.


I have a workbook over about 15 sheets including charts.

When you open it up you can only see the "INTERFACE" sheet and use buttons to select all other sheets / reports / charts.

I have been manually selecting each sheet and unhiding, then the "HOME" button selects each sheet and hides it again.

What I am trying to do is clean up my CODE and reduce the fat, so I have been looking for a bit of code that would select each sheet in turn and unhide it (and in reverse)


The best i have managed so far sort of worked but keeps failing on the charts. It would also be good if the home button passworded each sheet as well.


Thanks in advance

 

[Redwolfx]

A Quick for Loop

To unhide

Dim WS as Worksheet

For each WS in Thisworkbook.worksheets
  If WS.name <> "Whatever sheet you don't want hidden" then
      WS.Visible = xlSheetVisible
      WS.Unprotect "Password"
  End if
Next WS

To Hide

For each WS in Thisworkbook.worksheets
  If WS.name <> "Whatever sheet you don't want hidden" then
      WS.Visible = xlSheetHidden
      WS.Protect "Password"
  End if
Next WS

 

[Michael M]

Can you post the code you already have and we'll help you modify it.

 

[=ODIN=]

Sub hideEm()
    
    For x = 1 To ActiveWorkbook.Worksheets.Count
        If LCase(ActiveWorkbook.Worksheets(x).Name) <> LCase("interface") Then
            ActiveWorkbook.Worksheets(x).Visible = xlHidden
        End If
    Next x
    
    For Z = 1 To ActiveWorkbook.Charts.Count
            ActiveWorkbook.Charts(Z).Visible = xlHidden
    Next Z
End Sub
Sub showEm()
    For x = 1 To ActiveWorkbook.Worksheets.Count
        If LCase(ActiveWorkbook.Worksheets(x).Name) <> LCase("interface") Then
            ActiveWorkbook.Worksheets(x).Visible = True
        End If
    Next x
    
    For Z = 1 To ActiveWorkbook.Charts.Count
            ActiveWorkbook.Charts(Z).Visible = True
    Next Z
End Sub