hide sheet on file open
 

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

hide sheet on file open
 

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


"Hein" wrote:

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

hide sheet on file open
 

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

On Mon, 20 Oct 2008 03:18:00 -0700, Hein
wrote:

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

hide sheet on file open
 

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


"Gord Dibben" wrote:

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

On Mon, 20 Oct 2008 03:18:00 -0700, Hein
wrote:

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

hide sheet on file open
 

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.

Hein wrote:

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

"Gord Dibben" wrote:

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

On Mon, 20 Oct 2008 03:18:00 -0700, Hein
wrote:

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



--

Dave Peterson

hide sheet on file open
 

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




On Mon, 20 Oct 2008 22:51:01 -0700, Hein
wrote:

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


"Gord Dibben" wrote:

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

On Mon, 20 Oct 2008 03:18:00 -0700, Hein
wrote:

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

hide sheet on file open
 

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

On Mon, 20 Oct 2008 22:51:01 -0700, Hein
wrote:

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


"Gord Dibben" wrote:

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

On Mon, 20 Oct 2008 03:18:00 -0700, Hein
wrote:

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