Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets when book is opened
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets when book is opened
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets when book is opened
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets when book is opened
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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL WORK BOOK DUPLICATES WHEN OPENED | Excel Discussion (Misc queries) | |||
Auto sort when worksheet/book opened | Excel Discussion (Misc queries) | |||
Create New Workbook - Name book - 4 Sheets - Name Sheets | Excel Programming | |||
Strange error if another Excel book is opened | Excel Programming | |||
how to make a macro and run in every excel book that opened? | Excel Programming |