MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hiding/Unhiding Graph Sheets


Posted by Stan on October 22, 2001 9:27 AM

Mark O'Brien had provided me with the following code on this message board to hide all but one worksheet. I've adapted it to my needs and it has worked like a charm.

But I just ran into a "snag". I want to hide all but a chart sheet. In the SheetVisible sub, it cycles through all of the sheets and sets my sheet graph to visible and hides all others. But as soon as it reaches the last sheet name I get an error - "Method 'Visible' of Object '_Chart' failed." I would expect this if it had tried to close all sheets (at least one sheet should be open) but it did set my chart sheet (26th of 30 sheets) to visible at the line:

If Sheets(i).Name = sht Then
Sheets(i).Visible = True

Any ideas to get me pointed in the right direction?

Stan

Public Sub SheetGuide()
' Declare Variable
Dim sht As String
' Initialise Variable
sht = "Sheet3"
' Call Routine that makes sheet visible
SheetVisible sht

End Sub
Private Sub SheetVisible(ByVal sht As String)
Dim shtVisible As String
Application.ScreenUpdating = False
For i = 1 To Sheets.count
' Excel always needs to have at least 1 sheet visible
If Sheets(i).Name = Sheets.Item(1).Name Then
If Sheets(i).Name <> sht Then
Sheets.Item(1).Visible = True
i = i + 1
End If
End If

If Sheets(i).Name = sht Then
Sheets(i).Visible = True
Else
Sheets(i).Visible = False
End If
Next i
If Sheets.Item(1).Name <> sht Then
Sheets.Item(1).Visible = False
End If

End Sub


Posted by Stan on October 22, 2001 9:30 AM

Should read Re: Hiding/Unhiding Chart Sheets (Mark O'Brien?)

Posted by Mark O'Brien on October 22, 2001 12:04 PM

Stan, I can't remember the exact details of the original problem, but looking at my code I must have been having a bad day. I put a chart into Excel as an entire sheet and I've had no problem running the code that I gave you. However, I've also refined the code and it's a little bit tidier. You can change the the SheetVisible routine to this:

Private Sub SheetVisible(ByVal sht As String)

Application.ScreenUpdating = False
' Excel always needs to have at least 1 sheet visible
' Set the sheet to be visible first
Sheets(sht).Visible = True

For i = 1 To Sheets.Count
If Not Sheets(i).Name = sht Then Sheets(i).Visible = False
Next i

End Sub

As far as I can tell, Excel treats Chart sheets the same as worksheets so long as you use the "Sheets" collection like I have done. If you still have problems then repost.