How to create buttons to open/go to certain sheets in Excel?
Sometimes, you may need to create a button to open a certain worksheet in current workbook, so how to deal with it? Actually there are several methods that can help you solve it.
- Create button to open certain sheet with shape and hyperlink
- Create button to open certain sheet with Form control button
- Create buttons to open all sheets with Kutools for Excel
This method will guide you to insert a rounded rectangle, and then add hyperlink for this rounded rectangle to the certain worksheet. You can do as follows:
1. Click Insert > Shapes > Rounded Rectangle. See screen shot:
2. Draw a rounded rectangle on the worksheet, and you can format it and type text to it as you need. In our case, we type Go To Sheet1 as below screen shot shown:
3. Right click the rounded rectangle, and select Hyperlink from the right-clicking menu.
4. In the opening Insert Hyperlink dialog box,
(1) Select Place in This Document in the Link to section;
(2) Click to select the certain sheet name in the Or Select a place in this document section;
(3) Type the destination cell address into the Type the cell reference box, and at last click the OK button.
Now when you click the rounded rectangle, it will skip to the specified cell of specified sheet at once.
This method will walk you through creating a form control button to open certain worksheet. You can do as follows:
1. Click the Developer > Insert > Button in Form Controls section. See screen shot:
Note: By default the Developer tab does not display in Ribbon, click to know how to show/display developer tab in Excel Ribbon.
2. Draw a button, then an Assign Macro dialog box comes out. In the dialog box, type a name for the macro into the Macro name box, and then click the New button.
3. Now the Microsoft Visual Basic for Applications dialog box opens, please paste following code between sub and End sub. See screen shot:
Code: Go to certain worksheet in Excel
Note: In the code, please change the Sheet1 to required sheet name based on your needs.
4. Save the code, and close the Microsoft Visual Basic for Application window. Now you will get a form control button as below screen shot shown. Click the button, it will skip to the specified worksheet immediately.
Both above methods can only create one button to open one sheet. If you want to batch create multiple buttons to open all worksheets, you should try Kutools for Excel’s Create List of Sheet Names utility.
1. Click the Kutools Plus > Worksheet > Create List of Sheet Names.
2. In the Create List of Sheet Names dialog box,
(1) Check the Contains buttons and macros option in the Sheet Index Styles section;
(2) In the Specify sheet name for Sheet Index box, type a name for the new created sheet;
(3) Specify the position of index sheet in the Insert the Sheet index in drop down list;
3. Click the OK button. Then it will create an index sheet with multiple buttons. Each button is named with its worksheet name and linked to this worksheet. See below screen shot:
Note: This method requires enabling the Trust to the VBA project object model option in the Trust Center.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agoWhy does Everyone still use these buttons designed 30 years ago, making your own ribbon is way cooler, check out the free solution at easyribbonbuilder.com
- To post as a guest, your comment is unpublished.· 1 years agoIs there a way in which once you navigate to a sheet the other sheets are closed?
- To post as a guest, your comment is unpublished.· 1 years agoIS THERE ANY OPTION TO MAKE A HYPERLINK IN ALL PAGES SAME TIME TO GO TO THE FIRST SHEET?
- To post as a guest, your comment is unpublished.· 2 years agoIs there a way to make the "cell reference" work with an offset formula?
For example, every week I add new data in the next column of row 5 and I'd like to navigate to the most recent data. Normally I'd use (OFFSET($B5,0,MAX(0,COUNT($B5:$Z5)-1))). B5 would represent data for the week of 1/6/2018, C5 represents 1/13/2018, D6 is 1/20/2018, and so on. I'd like the button to find the last cell between B5 and Z5 where data was entered.
- To post as a guest, your comment is unpublished.· 1 years agoDid you figure this out?
- To post as a guest, your comment is unpublished.· 3 years agoThank you soooooooooooooo much it is very easy and helpful for me
- To post as a guest, your comment is unpublished.· 3 years agoHI,
Thank you so much !!!. Very helpful !!! :-)
- To post as a guest, your comment is unpublished.· 4 years agoThanks,
For elaborating in very easy steps.
It is very helpful.