Definition of VBA Hyperlink

The hyperlink is commonly used with websites for navigating from one page to another or one website to another on the internet. In a similar way, we can control the movements within excel worksheet too. The different operations that can be performed in Excel are:

Moving to a specific location within the current workbook.
Opening different documents and select a mentioned area within the document.
Navigating to webpages from the worksheet.
Sending email to a defined address.

The hyperlink is easy to recognize because of its color change, mostly in blue. There exist different methods to create a hyperlink in excel and let using VBA.

How to Create a Hyperlink in Excel Using VBA Code?

You can add a hyperlink to a text or one sheet to another worksheet within excel using hyperlink add property. The format needs to be followed by specifying where the hyperlink should be created and navigation URL etc.

Format for VBA Hyperlink Add

The format shows the parameters need to be provided to add a hyperlink to a worksheet.

Anchor: Defines the cell you want to create the hyperlink.
Address: The URL to which the navigation should move.
[SubAddress]: Subaddress of the URL.
[ScreenTip]: The mouse pointer value to be showed while placing a mouse pointer.
[Text to Display]: The text needs to be displayed on the cell.

Use the Active cell property to add a hyperlink.

Select the add method from the list

Examples to Create Hyperlinks in Excel VBA

Below are the different examples to create hyperlinks in excel using VBA code.

You can download this VBA Hyperlink Excel Template here – VBA Hyperlink Excel Template

Example #1 – Creating a hyperlink from the Worksheet to a website

We want to create a hyperlink from worksheet named sub to a website using VBA code in excel.

Below are the steps to create a hyperlink in Excel VBA:

Step 1: Create a function named hyper to add the hyperlink.

Code:

Private Sub hyper()

End Sub

Step 2: Use the Active cell object to get open the hyperlink add method.

Code:

Private Sub hyper()

ActiveCell.Hyperlinks.Add(

End Sub

Step 3: Provide the parameter values to the hyperlink add method.

Code:

Private Sub hyper()

ActiveCell.Hyperlinks.Add Anchor:=Sheets("sub").Range("A1"), Address:="https://www.educba.com/", SubAddress:="", ScreenTip:="it is a Hyperlink", TextToDisplay:="Excel Training"

End Sub

Anchor: name of the worksheet
Address: Hyperlink to where the control to be navigated, given the website address
ScreenTip: The mouse pointer text
TextToDisplay: To which text the hyperlink is to be assigned

Step 4: Hit F5 or Run button under VBE to run this code and see the output.

The cell range A1 is selected and the text is assigned with a hyperlink, once you click on the text “Excel Training” it will redirect to the website https://www.educba.com/. When you move the mouse pointer next to the text it will show the mouse pointer text.

Example #2 – Hyperlink to Connect Two Worksheets

We have two worksheets named Home and sub. Let’s try to create a hyperlink from sub to home using VBA code.

Follow the below steps to create a hyperlink from one worksheet to another within the same workbook using the VBA code.

Step 1: Create a function, where we will write all codes to perform the action. Write code to select the worksheet ‘sub’ using the selection method of the worksheet.

Code:

Private Sub hyper1()

Worksheets("sub").Select

End Sub

Since the control moves within the sheet, it is necessary to select the worksheet in which you are creating the hyperlink.

Step 2: Select the cell range within the sheet where the hyperlink is want to create.

Code:

Private Sub hyper1()

Worksheets("sub").Select
Range("A1").Select

End Sub

Step 3: Now let’s add the hyperlink using the active cell property.

Code:

Private Sub hyper1()

Worksheets("sub").Select
Range("A1").Select
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Home'!A1", TextToDisplay:="Click to move home sheet"

End Sub

Since the worksheet is already selected, Anchor is given as ‘Selection’. The hyperlink is specified as ‘Home’ sheet and range A1.

Step 4: Run the code and sheet sub will be shown the hyperlink as below.

Step 5: Once the text is clicked the control will be moved to the ‘Home’ sheet. And cell A1 will be selected.

Example #3 – Hyperlink with Multiple Worksheets

If you want to create hyperlink across multiple worksheets it is also possible. In this example, we have multiple sheets within the same workbook. Different type of excel functions exists so from the main worksheet ‘Functions’. Let’s try to create a hyperlink to the different worksheet named with different functions using VBA code:

The multiple worksheets are named as below with different excel function names

Since we want to create a hyperlink to each worksheet it’s difficult to repeat the code. Follow the below steps to create a hyperlink using VBA Code in Excel:

Step 1: Create a variable to deal with worksheet easily.

Code:

Private Sub hyper2()

Dim ws As Worksheet

End Sub

Step 2: Now we want to select the main page which acts as an index page and select the cell range A1.

Code:

Private Sub hyper2()

Dim ws As Worksheet
Worksheets("Functions").Select
Range("A1").Select

End Sub

Step 3: To move through multiple worksheet and hyperlink we are creating a for each loop. A1 is already selected as active cell so creating a hyperlink from this.

Code:

Private Sub hyper2()

Dim ws As Worksheet
Worksheets("Functions").Select
Range("A1").Select

For Each ws In ActiveWorkbook.Worksheets
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell
Next ws

End Sub

Step 4: Provide the parameter values to create a hyperlink for each worksheet. Since hyperlink starts from active cell anchor=Active cell, the address is given as ” “.

Code:

Private Sub hyper2()

Dim ws As Worksheet
Worksheets("Functions").Select
Range("A1").Select

For Each ws In ActiveWorkbook.Worksheets
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=""
Next ws

End Sub

Step 5: The hyperlink is looped through worksheet so we should give subaddress as sheet names. To get the sheet names we can use the variable ws and cell range as A1. The sheet name will have referred with a single quotation. Sheet name and range will be specified and also closed with a single quotation.

Code:

Private Sub hyper2()

Dim ws As Worksheet
Worksheets("Functions").Select
Range("A1").Select

For Each ws In ActiveWorkbook.Worksheets
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & ws.Name & "!A1" & ""
Next ws

End Sub

Step 6: To get the hyperlink with sheet name gives TextToDisplay as ws.Name

Code:

Private Sub hyper2()

Dim ws As Worksheet
Worksheets("Functions").Select
Range("A1").Select

For Each ws In ActiveWorkbook.Worksheets
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & ws.Name & "!A1" & "", TextToDisplay:=ws.Name
Next ws

End Sub

This code will store hyperlink for each worksheet in the same cell A1.

Step 7: To change this each sheet to different cell down one cell from the active cell.

Code:

Private Sub hyper2()

Dim ws As Worksheet
Worksheets("Functions").Select
Range("A1").Select

For Each ws In ActiveWorkbook.Worksheets
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & ws.Name & "!A1" & "", TextToDisplay:=ws.Name
ActiveCell.Offset(1, 0).Select
Next ws

End Sub

Step 8: Run the program and each sheet name will be displayed on the sheet ‘Functions’ and while clicking on it the control will move to the corresponding sheet.

Things to Remember

Hyperlink property of active cell used to create hyperlinks in VBA.
Hyperlink help to move within the workbook easily.

Definition of VBA Hyperlink

How to Create a Hyperlink in Excel Using VBA Code?

Examples to Create Hyperlinks in Excel VBA

Example #1 – Creating a hyperlink from the Worksheet to a website

Example #2 – Hyperlink to Connect Two Worksheets

Example #3 – Hyperlink with Multiple Worksheets

Things to Remember

Recommended Articles

About Us

Apps

Resources

Certification Courses