hide sheet on file open

[Hein]

How can I set a file that when I open it certain sheets are hidden and others
not?
Thanks

 

[Mike H]

Hi,

I would suggest you hide the sheets before closing but this does require
the workbook to be saved

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Visible = xlVeryHidden
ThisWorkbook.Save
End Sub

If saving is undesirable then do it on open but if macros aren't enabled
then this fails

Private Sub Workbook_Open()
Sheets("Sheet1").Visible = xlVeryHidden
End Sub

Mike

 

[Gord Dibben]

Either save it with the sheets hidden or use code in Thisworkbook module.

Private Sub Workbook_Open()
Sheets(Array("Sheet1", "Sheet3", "Sheet5")) _
..Visible = False
End Sub


Gord Dibben MS Excel MVP

 

[Hein]

Thanks very much. I got it to work with the following code:
Private Sub workbook_open()
'hide sheets
Application.ScreenUpdating = False

For Each sh In ThisWorkbook.Sheets
sh.Visible = False
Sheets("Contents").Visible = True
Next

The only problem that I encounter is that when the workbook is saved with
only the visible sheet showing and I re-open the workbook I get the following
error:
Run-time error '1004'
Unable to set the visible property of the worksheet class.

When I however make more sheets visible save and close it and then re-open
it, it works exactly how I want it to work.

I think that an If statement might work that will basicly ignore the code if
only the one sheet i.e. in my case "Contents" are visible. I am however not
sure how to write it in code.

Please assist.
Thanks

 

[Dave Peterson]

Make sure Contents is visible before you hide the others.

Option Explicit
Private Sub workbook_open()
dim sh as object
'hide sheets
Application.ScreenUpdating = False

me.Sheets("Contents").Visible = True

For Each sh In me.Sheets
if lcase(sh.name) = lcase("contents") then
'skip it
else
sh.Visible = False
end if
Next sh

application.screenupdating = true

End sub

(Untested, uncompiled. Watch for typos.

The Me keyword refers to the object that owns the code--in this case, it's the
workbook and is the same as ThisWorkbook.

 

[Gord Dibben]

Private Sub workbook_open()
'hide sheets
Dim sh As Worksheet
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Contents").Visible = True
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Contents" Then
sh.Visible = False
End If
Next
End Sub

 

[Gord Dibben]

Forgot to reenable screenupdating.

Private Sub workbook_open()
'hide sheets
Dim sh As Worksheet
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Contents").Visible = True 'make sure visible first
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Contents" Then
sh.Visible = False
End If
Next
Application.ScreenUpdating = True
End Sub


Gord