How to create buttons to move between sheets

Today's tip is going to focus on a simple Macro and button that should help you design your larger sheets to be easier to move around for yourself and others.

My Sample large sheet has 5 whole tabs.


We're going to use the control toolbox for this one (right click in the blank space up at the top near the help box, and enable control toolbox





Once the control toolbox comes up, we're going to use the Command Button





Click on the button, and then click in your spreadsheet



Right click, and select properties. Your interested in two specific options at this point -> Caption, and (name).



(name) is what excel calls the button - caption is what you and your users see on the button. I'm going to create 4 buttons on the summary Tab. One for each sheet. I am going to name them salesButton, viewsButton, daysButton, and listingButton. The captions will be Sales, Views, Days, and Listings
Once you've set up the buttons, while still in design mode (the little right angle ruler clicked) double click on your button. This will bring up the visual basic screen. You'll see a lot of things you don't need at this point. All you are concerned with is the main window. For the Sales button, you'll see the following:


The code to move between sheets is very straightforward. Sheets("Sheetname").select

So to select the Sales sheet, we'll input sheets("Sales").select

That's all you need! Now when you turn off design mode (uncheck the design button) and click on the button, it will select the Sales sheet! This may seem pretty mundane for my 5 spreadsheet book, but when you start having dozens of sheets, creating a few buttons on the top or bottom of each sheet for your users to get back to where they came from, or where they need to be going, will make this a LOT easier to use. Just make sure you advise people not to change the sheet names on you, or things will stop working.