Use of Button to switch from Sheet to Sheet

rbifi

New Member
Joined
Dec 22, 2004
Messages
14
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
You could use Control-PgUp and Control-PgDn to go backward and forward one sheet. Hope that helps!
 

rbifi

New Member
Joined
Dec 22, 2004
Messages
14
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Active Member
Joined
Dec 12, 2003
Messages
351

ADVERTISEMENT

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

New Member
Joined
Dec 22, 2004
Messages
14
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

Active Member
Joined
Dec 12, 2003
Messages
351

ADVERTISEMENT

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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Right click the forward/Reverse buttons down by your Sheet Tabs. You will get a list of your Sheets you can pick from.
 

smokenack

Active Member
Joined
Dec 12, 2003
Messages
351
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

Legend
Joined
May 15, 2003
Messages
29,536
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
 

Forum statistics

Threads
1,148,050
Messages
5,744,513
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top