Hide sheets when book is opened

I have a work book with a main page that I would like to be the only page the
user sees when they open the book. This page has command buttons to jump to
the other seven pages. I would like to add some code to the buttons to unhide
the pages and then jump to that page.

Could anyone help with this please.

Thanks in advance for any responses.

I don't believe that you can hide sheets. You can hide a workbook. I am no
expert, but I would suggest using 8 different workbooks (1 for the "Main"
page, and 7 one Sheet workbooks). Double click your ThisWorkbook object in
your Project explorer
Private Sub Workbook_Open()

Application.DisplayAlerts = False 'if individual workbooks contain there own
macros
Workbooks.Open Filename:="C:/FirstBook.xls"
Workbooks.Open Filename:="C:/SecondBook.xls"
etc...
End Sub

the sheets that are now the individual workbook should have the following
code in the ThisWorkbook Object:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next 'it may already be visible
Windows("FirstBook.xls").Visible = False
End Sub

Your CommandButtons should unhide the workbook.
Sub CommandButton1_Click()
Windows("FirstBook.xls").Visible = True
end sub

I'm sure that someone will give you a better solution, but It's the best I
can do. Good luck.



"Mark Dullingham" wrote:

> I have a work book with a main page that I would like to be the only page the
> user sees when they open the book. This page has command buttons to jump to
> the other seven pages. I would like to add some code to the buttons to unhide
> the pages and then jump to that page.
>
> Could anyone help with this please.
>
> Thanks in advance for any responses.

Mark, this should get you started,

Put this in the thisworkbok code, will hide all sheets except sheet1 when
you open the workbook, if macros are enabled

Private Sub Workbook_Open()
Dim sh As Worksheet
For Each sh In Worksheets
If Not sh.Name = "Sheet1" Then
sh.Visible = xlSheetVeryHidden
End If
Next sh
End Sub

Then use something like this for your buttons

Sub GoTo_Sheet2()
Sheets("Sheet2").Visible = True
Sheets("Sheet2").Select

End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Mark Dullingham" <[email protected]> wrote in
message news:[email protected]...
> I have a work book with a main page that I would like to be the only page
the
> user sees when they open the book. This page has command buttons to jump
to
> the other seven pages. I would like to add some code to the buttons to
unhide
> the pages and then jump to that page.
>
> Could anyone help with this please.
>
> Thanks in advance for any responses.

Hi Paul

Thanks for your help the work book code worked a treat, Just what I was
looking for.
The code for the button ended up like this

Private Sub CommandButton1_Click()
Sheets("Sheet 1").Visible = True
Sheets("Sheet 1").Select
End Sub

Once again thanks for your help

Mark

"Paul B" wrote:

> Mark, this should get you started,
>
> Put this in the thisworkbok code, will hide all sheets except sheet1 when
> you open the workbook, if macros are enabled
>
> Private Sub Workbook_Open()
> Dim sh As Worksheet
> For Each sh In Worksheets
> If Not sh.Name = "Sheet1" Then
> sh.Visible = xlSheetVeryHidden
> End If
> Next sh
> End Sub
>
> Then use something like this for your buttons
>
> Sub GoTo_Sheet2()
> Sheets("Sheet2").Visible = True
> Sheets("Sheet2").Select
>
> End Sub
>
>
> --
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
> "Mark Dullingham" <[email protected]> wrote in
> message news:[email protected]...
> > I have a work book with a main page that I would like to be the only page
> the
> > user sees when they open the book. This page has command buttons to jump
> to
> > the other seven pages. I would like to add some code to the buttons to
> unhide
> > the pages and then jump to that page.
> >
> > Could anyone help with this please.
> >
> > Thanks in advance for any responses.
>
>
>

Glad it worked out for you

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Mark Dullingham" <[email protected]> wrote in
message news:[email protected]...
> Hi Paul
>
> Thanks for your help the work book code worked a treat, Just what I was
> looking for.
> The code for the button ended up like this
>
> Private Sub CommandButton1_Click()
> Sheets("Sheet 1").Visible = True
> Sheets("Sheet 1").Select
> End Sub
>
> Once again thanks for your help
>
> Mark
>
> "Paul B" wrote:
>
> > Mark, this should get you started,
> >
> > Put this in the thisworkbok code, will hide all sheets except sheet1
when
> > you open the workbook, if macros are enabled
> >
> > Private Sub Workbook_Open()
> > Dim sh As Worksheet
> > For Each sh In Worksheets
> > If Not sh.Name = "Sheet1" Then
> > sh.Visible = xlSheetVeryHidden
> > End If
> > Next sh
> > End Sub
> >
> > Then use something like this for your buttons
> >
> > Sub GoTo_Sheet2()
> > Sheets("Sheet2").Visible = True
> > Sheets("Sheet2").Select
> >
> > End Sub
> >
> >
> > --
> > Paul B
> > Always backup your data before trying something new
> > Please post any response to the newsgroups so others can benefit from it
> > Feedback on answers is always appreciated!
> > Using Excel 2002 & 2003
> >
> > "Mark Dullingham" <[email protected]> wrote in
> > message news:[email protected]...
> > > I have a work book with a main page that I would like to be the only
page
> > the
> > > user sees when they open the book. This page has command buttons to
jump
> > to
> > > the other seven pages. I would like to add some code to the buttons to
> > unhide
> > > the pages and then jump to that page.
> > >
> > > Could anyone help with this please.
> > >
> > > Thanks in advance for any responses.
> >
> >
> >