Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
You may find yourself in a situation where you want to use a button to get to a certain worksheet. Why? Well, maybe you want to be able to label it specially. With the default tab, you have limitations when it comes to formatting. Perhaps the name of the report requires more definition.
Here I will walk you through how I set up my sheet tabs as buttons with two different methods—one using shapes and hyperlinks and the other using command buttons and macros.
Adding the Button With a Shape
To create a button from a shape, you must be in the insert tab, then click on the illustrations button. Next, click on shapes and find the shape that you would like to use for a button. I'm using a standard rectangle.
Add the button by tracing the shape to size with the cursor. Make adjustments to the size of the button by pulling at the spheres on the corners and sides of the shape.
Add text to the shape by right-clicking on and selecting edit text from the drop-down menu. This will put the cursor into the shape where you can add text and format it appropriately. As you will see later on, I have created a button for every tab.
Linking the Button to a Hyperlink
To begin linking your newly created button, right-click on the button and select the link from the drop-down menu.
The insert hyperlink window will appear. Click on the "place in the document" option and then select the sheet that you want to assign to the button. Lastly, add the location that you would like the cursor to end up in after the operation has been completed or leave it blank.
The button should be completely operational at this point. To review an option to add a command button with a macro, continue reading.
Adding a Command Button
To add a command button, make sure that you have the developer tab enabled in Excel. With the developer tab open, click on insert, then choose the command button option.
Next, draw the shape of the button with your cursor.
After the assign macro button window appears, click on the "new" button to add a macro code to open a sheet.
Next, add the text below with the sheet that you want to be directed to in quotations. The text must be added between the line that starts with sub and the line that starts with end sub.
To be able to test the button, make sure that you click on the design mode button to disable it.
Saving the Document
Make sure to save the document as a macro-enabled workbook, or the macros that you create will not work after the document is closed.
Editing the Button
After the button is created, you can change the size of it by pulling the spheres on each corner and side. The text can be changed by right-clicking on the button in design mode and selecting the command button object. This will open up the edit option, which must also be clicked.
Changing other properties of the button, like the size of the text, is tricky. Right-clicking on the button and choosing the properties option from the drop-down will give these font options that are outlined in red below. There are also dozens of other settings in properties that allow you to manipulate the button appearance.
To learn more about developing in Excel, I recommend taking a look at the book Microsoft Excel 2019 VBA and Macros (Business Skills) by Bill Jelen to enhance your skills.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2021 Joshua Crowder