Macro to advance to next worksheet

[Android]

I have a workbook (2007) with about 55 worksheets. I would like to do any one of the following:

On the main worksheet "home page" add a macro that allows me to select which worksheet I want to go to, and when taken to that worksheet have another macro that takes me back to the "home page".

Or (more ideally)

On the "home page" have a macro that allows me to go to a specific worksheet, either through a pop up, or a scroll bar. And on each of the worksheets have 3 buttons namely <Previous> <Home> <Next>

Any ideas?

Thanks in advance.

 

[Bill_Biggs]

Welcome to the board, Android. Short of going to each sheet and creating a button, try this macro.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> macro1()<br><br><SPAN style="color:#00007F">Dim</SPAN> MyPrompt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyTitle <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>Y = "Sheet"<br>MyPrompt = "Enter Sheet Number You Wish."<br>MyTitle = "* * * Enter Number Only * * *"<br><br>yesno = MsgBox("Do you wish to go to the homepage?", vbYesNo)<br><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> yesno<br><SPAN style="color:#00007F">Case</SPAN> vbYes<br>Worksheets("Home Page").Select<br><SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Case</SPAN> vbNo<br>Response = Application.InputBox(Prompt:=MyPrompt, _<br>    Title:=MyTitle)<br>   <br>    <SPAN style="color:#00007F">If</SPAN> Response = "False" <SPAN style="color:#00007F">Then</SPAN> MsgBox "Program Cancelled"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Response = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    Z = Response<br><br>Sheets("Sheet" & (Z)).Select<br><SPAN style="color:#00007F">End</SPAN> Select<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Every time you want to change sheets, activate the macro, otherwise, create a button one ach sheet that goes to this macro. This macro assumes that your homepage is named "Home Page" and the other sheets do not have unique names, although I believe that even if they do, this will still work on them.

If you do go to the trouble of going to each page to place a button on it, you might as well create a link to the homepage next to the button, and then you will need to only to include this in the macro:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> macro1()<br><br><SPAN style="color:#00007F">Dim</SPAN> MyPrompt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyTitle <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>Y = "Sheet"<br>MyPrompt = "Enter Sheet Number You Wish."<br>MyTitle = "* * * Enter Number Only * * *"<br><br><br><SPAN style="color:#00007F">Case</SPAN> vbNo<br>Response = Application.InputBox(Prompt:=MyPrompt, _<br>    Title:=MyTitle)<br>   <br>    <SPAN style="color:#00007F">If</SPAN> Response = "False" <SPAN style="color:#00007F">Then</SPAN> MsgBox "Program Cancelled"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Response = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    Z = Response<br><br>Sheets("Sheet" & (Z)).Select<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH's, and again, welcome!

 

[Dougie1]

Do you really need a macro ??

Why not just navigate between sheets by right clicking on the directional arrows at the very bottom left of the sheet and using the sheet menu there to move between sheets.

Cheers,

D

 

[Android]

Bill_Biggs - Thank you for the input, very much appreciated, and it works.

Dougie1 - You are right, but with a Macro it is easier to navigate.

Thanks again guys, very useful resource.