How to Create Button to Link to Another Sheet in Excel (3 Ways)

We will show you how to create a button to link to another sheet by inserting Shapes, using the Button from Form Controls, and the Command Button from ActiveX Controls in Excel. We have 3 Sheets containing the Product name and Sales for 3 consecutive months, named January, February, and March, respectively. Here’s the dataset for January.

Ways to Create a Button to Link to Another Sheet in Excel

Here are the Sales for February.

And here is the dataset for Sales in March.


Method 1 – Inserting Shapes to Create a Button to Link to Another Sheet in Excel


Step 1 – Inserting a Shape

  • Go to the Insert tab and click on Illustrations.
  • Click on Shape and select Rectangle.

Inserting Shapes to Create Button to Link to Another Sheet in Excel

  • Insert a rectangle into the sheet by dragging.


Step 2 – Formatting the Shape

  • Go to the Shape Format tab.
  • Click on Shape Styles and click on the Theme Style button.

Formatting Shape to Create Button to Link to Another Sheet in Excel

  • Choose any Theme Style according to your preference. We will select Subtle Effect – Orange, Accent 2.

  • Type the name you want to give to your button. We will type January in the Shape.
  • Select the text and go to the Home tab.
  • Select 14 as Font Size and click on Bold to bold the text.

  • Select the shape and press Ctrl + C on your keyboard.

Using Keyboard Shortcut to Create Button to Link to Another Sheet in Excel

  • Press Ctrl + V to paste the shape.

  • Type February and March in those Shapes.


Step 3 – Creating a Link to Another Sheet

  • Select a shape and Right-click on it.
  • Click on Link.

Creating Link to Another Sheet to Create Button Using Shape

  • The Insert Hyperlink box will open.
  • Go to the Place in This Document Option and select January from Cell References.
  • Click on OK.

Opening Insert Hyperlink Box to Create Button to Link to Another Sheet in Excel

  • Create links for the other two Shapes with the sheets named February and March.
  • The shapes will work like buttons to go to another sheet.

Read More: Excel Hyperlink to Cell in Another Sheet with VLOOKUP


Method 2 – Using the Button from Form Controls to Create a Button to Link to Another Sheet


Step 1 – Inserting the Button from Form Controls

  • Go to the Developer tab and click on Insert.
  • Click on Button from Form Controls.

Using Button from Form Controls to Create Button to Link to Another Sheet

  • Insert a button like the image given below.

  • Type the name you want to give to your button. We will type January in the button.
  • Select the text and go to the Home tab.
  • Select 16 as Font Size and click on Bold to bold the text.


Step 2 – Assigning a Macro

  • Select the button and Right-click on it.
  • Click on Assign Macro.

Assigning Macro to Create Button to Link to Another Sheet in Excel Using Button from Form Controls

  • The Assign Macro box will open.
  • Set January as Macro name.
  • Click on New.

Opening Assign Macro Box to Create Button to Link to Another Sheet in Excel

  • A Sub procedure named January will be created.
  • Insert the following code in your Module.
Sub January()
ThisWorkbook.Sheets("January").Activate
End Sub

Code Breakdown

  • We activated a Sheet named January using the Activate method.
  • Save the code by clicking on the Save button.

  • Create a button named February by going through the steps given above.

  • Open the module and use the following code in your Module.
Sub February()
ThisWorkbook.Sheets("February").Activate
End Sub

Code Breakdown

  • We activated a Sheet named February using the Activate method.
  • Create a button named March by going through the steps given above.

  • Open the module and insert the following code in your Module.
Sub March()
ThisWorkbook.Sheets("March").Activate
End Sub

Code Breakdown

  • We activated a Sheet named March using the Activate method.
  • The buttons will be linked to other sheets that you have assigned.

How to Create Button to Link to Another Sheet in Excel Using Button from Form Controls


Method 3 – Use the Command Button from ActiveX Controls in Excel


Step 1 – Inserting the Command Button and Editing Properties

  • Go to the Developer tab and click on Insert.
  • Select Command Button from ActiveX Controls.

Use of Command Button from ActiveX Controls to Create Button to Link to Another Sheet

  • Insert a Command Button like the image given below.

Inserting Command Button and Editing Properties to Create Button to Link to Another Sheet in Excel

  • Select the Command Button and Right-click on it.
  • Click on Properties.

  • The Properties box will open.
  • Type January as Caption.

Opening Properties Box to Create Button to Link to Another Sheet in Excel

Read More: How to Add Hyperlink to Another Sheet in Excel


Step 2 – Assigning Code

  • Select the Command Button and Right-click on it.
  • Click on View Code.

Assigning Code to Create Button to Link to Another Sheet in Excel

  • A Private Sub will be created named CommandButton1_Click.
  • Copy the following code in your Command Button.
Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("January").Activate
End Sub

Code Breakdown

  • We activated a Sheet named January using the Activate method.
  • Create a button named February by going through the steps given above.

  • Open another Private Sub named CommandButton2_Click() and use the following code.
Private Sub CommandButton2_Click()
ThisWorkbook.Sheets("February").Activate
End Sub

Code Breakdown

  • We activated a Sheet named February using the Activate method.
  • Create a Command Button named March by going through the steps given above.

  • Open another Private Sub named CommandButton3_Click() and use the following code.
Private Sub CommandButton3_Click()
ThisWorkbook.Sheets("March").Activate
End Sub

Code Breakdown

  • We activated a Sheet named March using the Activate method.
  • Click on Design Mode from the Developer tab.

  • The buttons will be linked to other sheets that you have assigned.

How to Create Button to Link to Another Sheet in Excel Using Command Button from ActiveX Controls

Read More: Excel Hyperlink to Another Sheet Based on Cell Value


Download the Practice Workbook


Related Articles

<< Go Back To Excel Hyperlink to Another Sheet | Hyperlink in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo