Hiding and Showing Sheets using Macro
 

Hi Does any have any ideas on how to create a macro for this or if this is
possible?

- I want to have 1 workbook with about 13 spreadsheets in it.
- When I open the workbook I only want to see "sheet 1" (with the other 12
spreadsheets hidden)
- On sheet 1 i want to have 3 buttons, 1. Commodities 2. Forex 3. Options
- When I click on the 'Commodities' button, I want spreadsheets 2,3,4 & 5 to
show/be unhidden
- I also want there to be a button on spreadsheet 2,3,4,&5 to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 2,3,4 & 5 hidden again (like when i first open the workbook)
- When i click on the 'Forex' button, I want spreadsheet 6,7,8 & 9 to be
shown/unhidden
- I also want there to be a button on spreadsheet 6,7,8 & 9 to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 6,7,8 & 9 hidden again (like when i first open the workbook)
- When i click on the 'Options' button, I want spreadsheet 10,11 & 12 to be
shown/unhidden
- I also want there to be a button on spreadsheet 10,11 & 12to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 10,11 & 12hidden again (like when i first open the workbook)

I'm not sure if what i want to do is even possible and would really
appreciate any help on how to do this or on a better way to do this.

Many thanks in advance!

Hiding and Showing Sheets using Macro
 

hi,
very possible. i didn't write in all so you will have to do some creating on
you own, i am just showing you have to get started.
hide all sheets except 1 when you save and close the file. that way only one
sheet will be showing at open.
drop a command button on the sheet and put this code in it. it will unhide
the first five sheets and activate(select) sheet2. change if you want. this
same code can be use for your other 2 buttons except different sheet names.
Private Sub CommandButton1_Click()
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet2").Activate
End Sub

code to return to sheet1 and rehide sheets 2-5... this code can be use for
all the other return/rehide buttons except... again... different sheet names.
Private Sub CommandButton2_Click()
Sheets("sheet1").Activate
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = False
End Sub

hope this helps.

Regards
FSt1

"engbe" wrote:

Hi Does any have any ideas on how to create a macro for this or if this is
possible?

- I want to have 1 workbook with about 13 spreadsheets in it.
- When I open the workbook I only want to see "sheet 1" (with the other 12
spreadsheets hidden)
- On sheet 1 i want to have 3 buttons, 1. Commodities 2. Forex 3. Options
- When I click on the 'Commodities' button, I want spreadsheets 2,3,4 & 5 to
show/be unhidden
- I also want there to be a button on spreadsheet 2,3,4,&5 to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 2,3,4 & 5 hidden again (like when i first open the workbook)
- When i click on the 'Forex' button, I want spreadsheet 6,7,8 & 9 to be
shown/unhidden
- I also want there to be a button on spreadsheet 6,7,8 & 9 to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 6,7,8 & 9 hidden again (like when i first open the workbook)
- When i click on the 'Options' button, I want spreadsheet 10,11 & 12 to be
shown/unhidden
- I also want there to be a button on spreadsheet 10,11 & 12to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 10,11 & 12hidden again (like when i first open the workbook)

I'm not sure if what i want to do is even possible and would really
appreciate any help on how to do this or on a better way to do this.

Many thanks in advance!

Hiding and Showing Sheets using Macro
 

I took much the same, straightforward route that FSt1 took. I used Command
Buttons from the "Forms" toolbar rather than from the Controls toolbar, and
I've put some code in both the Workbook_Open() event and the 'main menu'
(3-button) sheet's Worksheet_Activate() code to save some lines of code
elsewhere. You can examine the workbook itself by downloading it from he
http://www.jlathamsite.com/uploads/for_engbe.xls



"FSt1" wrote:

hi,
very possible. i didn't write in all so you will have to do some creating on
you own, i am just showing you have to get started.
hide all sheets except 1 when you save and close the file. that way only one
sheet will be showing at open.
drop a command button on the sheet and put this code in it. it will unhide
the first five sheets and activate(select) sheet2. change if you want. this
same code can be use for your other 2 buttons except different sheet names.
Private Sub CommandButton1_Click()
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet2").Activate
End Sub

code to return to sheet1 and rehide sheets 2-5... this code can be use for
all the other return/rehide buttons except... again... different sheet names.
Private Sub CommandButton2_Click()
Sheets("sheet1").Activate
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = False
End Sub

hope this helps.

Regards
FSt1

"engbe" wrote:

Hi Does any have any ideas on how to create a macro for this or if this is
possible?

- I want to have 1 workbook with about 13 spreadsheets in it.
- When I open the workbook I only want to see "sheet 1" (with the other 12
spreadsheets hidden)
- On sheet 1 i want to have 3 buttons, 1. Commodities 2. Forex 3. Options
- When I click on the 'Commodities' button, I want spreadsheets 2,3,4 & 5 to
show/be unhidden
- I also want there to be a button on spreadsheet 2,3,4,&5 to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 2,3,4 & 5 hidden again (like when i first open the workbook)
- When i click on the 'Forex' button, I want spreadsheet 6,7,8 & 9 to be
shown/unhidden
- I also want there to be a button on spreadsheet 6,7,8 & 9 to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 6,7,8 & 9 hidden again (like when i first open the workbook)
- When i click on the 'Options' button, I want spreadsheet 10,11 & 12 to be
shown/unhidden
- I also want there to be a button on spreadsheet 10,11 & 12to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 10,11 & 12hidden again (like when i first open the workbook)

I'm not sure if what i want to do is even possible and would really
appreciate any help on how to do this or on a better way to do this.

Many thanks in advance!

Hiding and Showing Sheets using Macro
 

THANK YOU THANK YOU THANK YOU to the both of you!!! Both of you don't know
how much i appreciate this...and an extra THANK YOU to JLatham for taking the
time to actually create it in a work book and answering yet another one of my
questions : )

"JLatham" wrote:

I took much the same, straightforward route that FSt1 took. I used Command
Buttons from the "Forms" toolbar rather than from the Controls toolbar, and
I've put some code in both the Workbook_Open() event and the 'main menu'
(3-button) sheet's Worksheet_Activate() code to save some lines of code
elsewhere. You can examine the workbook itself by downloading it from he
http://www.jlathamsite.com/uploads/for_engbe.xls



"FSt1" wrote:

hi,
very possible. i didn't write in all so you will have to do some creating on
you own, i am just showing you have to get started.
hide all sheets except 1 when you save and close the file. that way only one
sheet will be showing at open.
drop a command button on the sheet and put this code in it. it will unhide
the first five sheets and activate(select) sheet2. change if you want. this
same code can be use for your other 2 buttons except different sheet names.
Private Sub CommandButton1_Click()
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet2").Activate
End Sub

code to return to sheet1 and rehide sheets 2-5... this code can be use for
all the other return/rehide buttons except... again... different sheet names.
Private Sub CommandButton2_Click()
Sheets("sheet1").Activate
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Sheets("Sheet5").Visible = False
End Sub

hope this helps.

Regards
FSt1

"engbe" wrote:

Hi Does any have any ideas on how to create a macro for this or if this is
possible?

- I want to have 1 workbook with about 13 spreadsheets in it.
- When I open the workbook I only want to see "sheet 1" (with the other 12
spreadsheets hidden)
- On sheet 1 i want to have 3 buttons, 1. Commodities 2. Forex 3. Options
- When I click on the 'Commodities' button, I want spreadsheets 2,3,4 & 5 to
show/be unhidden
- I also want there to be a button on spreadsheet 2,3,4,&5 to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 2,3,4 & 5 hidden again (like when i first open the workbook)
- When i click on the 'Forex' button, I want spreadsheet 6,7,8 & 9 to be
shown/unhidden
- I also want there to be a button on spreadsheet 6,7,8 & 9 to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 6,7,8 & 9 hidden again (like when i first open the workbook)
- When i click on the 'Options' button, I want spreadsheet 10,11 & 12 to be
shown/unhidden
- I also want there to be a button on spreadsheet 10,11 & 12to say 'Back to
Spreadsheet 1' and when clicked will navigate back to spreadsheet 1 and
spreadsheets 10,11 & 12hidden again (like when i first open the workbook)

I'm not sure if what i want to do is even possible and would really
appreciate any help on how to do this or on a better way to do this.

Many thanks in advance!