MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Problem with Hiding Worksheets

Posted by Stan on October 11, 2001 8:58 AM

I am using the following code to save a chart as a GIF file and then load it into an Image box within a Userform.

Set CurrentChart = Sheets("Create Charts Here").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Load *.gif file into Image box
Image1.Picture = LoadPicture(Fname)

Then I try to use the code below (courtesy of Juan) to hide all but one of my sheets in the Workbook. But when "Sheets(i).Name = "Create Charts Here"" I get the error message - Method 'Visible' of object'_WorkSheet' failed. Any ideas why the sheet "Create Charts Here" is causing a problem?

Public Sub SheetVisible(ByVal unhideName 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
' First make all sheet tabs visible
If Sheets(i).Name = Sheets.Item(1).Name Then
If Sheets(i).Name <> unhideName Then
Sheets.Item(1).Visible = True
i = i + 1
End If
End If
' Then close all but target sheet
If Sheets(i).Name = unhideName Then
Sheets(i).Visible = True
Sheets(i).Visible = False
End If
Next i

If Sheets.Item(1).Name <> unhideName Then
Sheets.Item(1).Visible = False
End If

End Sub

Posted by Jerid on October 11, 2001 11:38 AM

Does a chart, Image box, or some other control still have focas on that sheet at that point?

Try to select a range (application.range("A1").select) before you set the visible property of each sheet.

Good Luck.