Use of Button to switch from Sheet to Sheet

[rbifi]

I would like to use a "button" or several buttons inserted into a sheet to transfer from sheet to sheet. I can do this with the hyperlink, but I would like to use the button feature to make it more presentable. I have looked through my excel books but I have not found anything on this feature. I can insert a button on to a sheet but I do not know how to assign a funtion to the button.

Can someone please give me some direction? My head is starting to hurt!!!

Windows XP
Excel 00

Thanks.

 

[Tazguy37]

You could use Control-PgUp and Control-PgDn to go backward and forward one sheet. Hope that helps!

 

[rbifi]

That is a good idea and I did not know that!

But, I would like to create a "one click" solution for this. The individuals who will be using this file(report) are not computer friendly. We have to make this "soldier proof", if there is such a thing!!

Thanks.

 

[Smitty]

You can assign these to Forms Buttons:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> NextSheet()
    Application.SendKeys ("^{PGDN}")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> LastSheet()
    Application.SendKeys ("^{PGUP}")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[smokenack]

go to tools - macro - visual basic editor and insert a module.Copy the following short code into the module

Sub Sheet2 ()
Sheets("Sheet2").Select
End Sub

then assign this to your button. This will take you to sheet2 from any other sheet. adjust to suit your sheet names.

Nick

 

[rbifi]

That's it! I do not know what it is, but it worked!

I will now have to learn about Visual Basic Editor!!!

Thanks alot, you saved my wife and kids from a long night !

 

[smokenack]

To take this one stage further is it possible to have one button that takes you to the sheet name picked from a drop down list,combo box etc. I'd love to be able to do this.

Nick

 

[Datsmart]

Right click the forward/Reverse buttons down by your Sheet Tabs. You will get a list of your Sheets you can pick from.

 

[smokenack]

Thanks I never knew that you could do that but it's a bit technical for the users of my workbooks. They would need a big red button if possible.

Nick

 

[Smitty]

For an End-User application, I generally build a User Form for sheet navigation, utilizing Option Buttons.

I.E.

OptionButton1_Click()
Sheets("Sheet1").Activate
End Sub

But that can be cumbersome with a lot of sheets, so if you'll have a ton of sheets (I hadn't thought of this method before) I'll bet you could quickly convert Jon Walkenbach's http://j-walk.com/ss/excel/tips/tip48.htm to sheet activation, rather than Printing, by switching out the Check Boxes for Option Buttons (and some changes to the code).

HTH,

Smitty