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

Looking for ways to know how to create a button to link to another sheet in Excel? We can make our work easier by creating links between sheets in Excel. Here, you will find 3 ways to create a button to link to another sheet in Excel.


Download Practice Workbook


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

Here, we will show you how to create a button to link to another sheet by inserting Shapes, using Button from Form Controls, and Command Button from ActiveX Controls in Excel.

We have 3 Sheets containing the Product name and Sales of 3 consecutive months and sheets are named January, February, and March respectively.

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

The dataset given above represents Sales in January.

This is the dataset for Sales in February.

Finally, The dataset given above represents Sales in March.


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

In the first method, we will show you how to create a button to link to another sheet in Excel by inserting Shapes. Follow the steps given below to do it on your own.


Step-01: Inserting Shape

Here, you will find a step-by-step way to insert Shape in Excel.

  • Firstly, go to the Insert tab >> click on Illustrations >> click on Shape >> select Rectangle.

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

  • Now, insert a rectangle into the sheet by dragging.


Step-02: Formatting Shape

Now, we will show you how you can format a Shape to create a button to link to another sheet. Here are the steps.

  • In the beginning, go to the Shape Format tab >> click on Shape Styles >> click on the Theme Style button.

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

  • Now, choose any Theme Style according to your preference. Here, we will select Subtle Effect – Orange, Accent 2.

  • Then, type the name you want to give to your button. Here, we will type January in the Shape.
  • After that, select the text >> go to the Home tab >> select 14 as Font Size >> click on Bold to bold the text.

  • Next, select the shape and press CTRL+C on your keyboard.

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

  • Now, press CTRL+V to paste the shape.

  • Then, type February and March in those Shapes.


Step-03: Creating Link to Another Sheet

Finally, you will find a way to link these shapes to other sheets in Excel here.

  • Firstly, select a shape and Right-click on it.
  • Then, click on the Link.

Creating Link to Another Sheet to Create Button Using Shape

Now, the Insert Hyperlink box will open.

  • After that, go to the Place in This Document Option >> select January from Cell References.
  • Next, click on OK.

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

  • Similarly, create links for the other two Shapes with the sheets named February and March.
  • Finally, the shapes will work like buttons to go to another sheet.


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

Now, you will find a way to create a button to link to another sheet using the Button from Form Control objects in Excel. Go through the steps given below to do it on your own.


Step-01: Inserting Button from Form Controls

Firstly, we will show you how to insert a button from Form Controls in Excel to create a button to link to another sheet. Here are the steps.

  • In the beginning, go to the Developer tab >> click on Insert >> click on Button from Form Controls.

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

  • Then, insert a button like an image given below.

  • After that, type the name you want to give to your button. Here, we will type January in the button.
  • Next, select the text >> go to the Home tab >> select 16 as Font Size >> click on Bold to bold the text.


Step-02: Assigning Macro

Now, we will assign Macro to this Button to create a link with another sheet. Follow the steps given below to do it on your dataset.

  • In the beginning, select the button and Right-click on it.
  • Then, click on Assign Macro.

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

  • Next, the Assign Macro box will open.
  • After that, set January as Macro name.
  • Then, click on New.

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

  • Now, a Sub procedure will be created named January.
  • Afterward, write the following code in your Module.
Sub January()
ThisWorkbook.Sheets("January").Activate
End Sub

Code Breakdown

  • Firstly, a Sub was already created named January.
  • Then, we activated a Sheet named January using the Activate method.
  • Next, save the code by clicking on the Save button.

  • Similarly, create a button named February by going through the steps given above.

  • Then, open the module similarly and write the following code in your Module.
Sub February()
ThisWorkbook.Sheets("February").Activate
End Sub

Code Breakdown

  • In the beginning, a Sub was already created named February.
  • Then, we activated a Sheet named February using the Activate method.
  • Again, create a button named March by going through the steps given above.

  • After that, Then, open the module similarly and write the following code in your Module.
Sub March()
ThisWorkbook.Sheets("March").Activate
End Sub

Code Breakdown

  • Firstly, a Sub was already created named March.
  • After that, we activated a Sheet named March using the Activate method.
  • Finally, 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


3. Use of Command Button from ActiveX Controls in Excel

In the final method, we will show you how to use Command Button from ActiveX Controls to create a button to link another sheet. Follow the steps given below to do it on your own.


Step-01: Inserting Command Button and Editing Properties

Here, you will find a way to insert a Command Button from ActiveX Controls and then edit its Properties.

  • Firstly, go to the Developer tab >> click on Insert >> select Command Button from ActiveX Controls.

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

  • Then, 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

  • After that, select the Command Button and Right-click on it.
  • Next, click on Properties.

  • Now, the Properties box will open.
  • Then, type January as Caption.

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


Step-02: Assigning Code

Now, we will assign code to create link to another sheet using this Command Button.

  • Again, select the Command Button and Right-click on it.
  • After that, click on View Code.

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

  • Now, a Private Sub will be created named CommandButton1_Click.
  • Next, write the following code in your Command Button.
Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("January").Activate
End Sub

Code Breakdown

  • Firstly, a Private Sub was already created named CommandButton1_Click.
  • Then, we activated a Sheet named January using the Activate method.
  • Similarly, create a button named February by going through the steps given above.

  • Then, open another Private Sub named CommandButton2_Click()  similarly and write the following code.
Private Sub CommandButton2_Click()
ThisWorkbook.Sheets("February").Activate
End Sub

Code Breakdown

  • In the beginning, a Private Sub was already created named CommandButton2_Click.
  • After that, we activated a Sheet named February using the Activate method.
  • Again, create a Command Button named March by going through the steps given above.

  • After that, open another Private Sub named CommandButton3_Click()  similarly and write the following code.
Private Sub CommandButton3_Click()
ThisWorkbook.Sheets("March").Activate
End Sub

Code Breakdown

  • Firstly, a Private Sub was already created named CommandButton3_Click.
  • Then, we activated a Sheet named March using the Activate method.
  • Next, click on Design Mode from the Developer tab.

  • Finally, 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


Conclusion

So, in this article, you will find 3 ways that will create a button to link to another sheet in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo