Updated date:

Create Buttons to Open Worksheets in Excel

Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.

create-buttons-to-open-worksheets-in-excel

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 in a special way. 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.

The illustration shows that number of steps taken to insert a shape.

The illustration shows that number of steps taken to insert a shape.

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.

The illustration above shows an inserted button without additional formatting.

The illustration above shows an inserted button without additional formatting.

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.

Shapes can be easily edited by right clicking on them and selecting the edit text option.

Shapes can be easily edited by right clicking on them and selecting the edit text option.

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.

Linking a button to view a sheet with a hyperlink is much easier than using a macro.

Linking a button to view a sheet with a hyperlink is much easier than using a macro.

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 completed or leave it blank.

By selecting the place in this document button you can have cursor moved to any part of an Excel document with the click of a button.

By selecting the place in this document button you can have cursor moved to any part of an Excel document with the click of a button.

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.

create-buttons-to-open-worksheets-in-excel

Next, draw the shape of the button with your cursor.

create-buttons-to-open-worksheets-in-excel

After the assign macro button window appears, click on the "new" button to add a macro code to open a sheet.

You also have to record macro option. With this option you would click a record button, record yourself clicking sheet tab, and clicking a stop button to end the recording. This is another option to assigning the macro to the button.

You also have to record macro option. With this option you would click a record button, record yourself clicking sheet tab, and clicking a stop button to end the recording. This is another option to assigning the macro to the button.

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.

ThisWorkbook.Sheets("Sheet1").Activate

create-buttons-to-open-worksheets-in-excel

To be able to test the button, make sure that you click on the design mode button to disable it.

To test or edit command buttons you must toggle in and out of design view.

To test or edit command buttons you must toggle in and out of design view.

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.

In order for macros to work in the future you must save the workbooks that they are saved to as macro-enabled workbooks.

In order for macros to work in the future you must save the workbooks that they are saved to as macro-enabled workbooks.

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 commandbutton object. This will open up the edit option, which must also be clicked.

create-buttons-to-open-worksheets-in-excel

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.

The illustration shows all of the formatting tools that can be used to change how the button is displayed.

The illustration shows all of the formatting tools that can be used to change how the button is displayed.

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

Related Articles