VBA Hyperlinks

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Aaron Crowe

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Hyperlinks are URL attached to a value that one may see when we hover the mouse over it. When we click on it, the URL opens. In VBA, we have an inbuilt property to create hyperlinks in VBA. To use this property, we must use the Add method together with the hyperlink statement to insert a hyperlink in a cell.

Even though we have Page Up and Page Down shortcut key in excel to move from one sheet to another. But it becomes complex when we have to move between 10 to more worksheets. It is where the beauty of “Hyperlinks in Excel” comes into the picture. The hyperlink is a predetermined URL that takes you to the respective cell or worksheet as assigned.

We all know how to quickly create hyperlinks in the worksheet to move from one sheet to another. But, of course, you can also go to any other sheet. But in today’s article, we will show you how to create hyperlinks using VBA coding.

Table of contents
VBA Hyperlinks

Let us look at the formula of the hyperlinks in Excel VBA.

VBA Hyperlink Formula
  • Anchor: In which cell would you like to create a hyperlink?
  • Address: What is the URL to the hyperlink to navigate?
  • : What is the location of the page?
  • : What is the value shown when you place a mouse pointer on the hyperlink name or cell?
  • : What test will display in the cell? For example, Worksheet Name.

Assume you want to create a VBA hyperlink to the “Main Sheet” sheet from the other sheet, “Example 1.”

VBA Hyperlinks Example 1

In the worksheet “Example 1” and cell A1, we will create the hyperlink using Code in VBA.

Step 1: First, select the cell A1 of the worksheet example 1.

Code:

Sub Hyperlink_Example1()

   Worksheets("Example 1").Select
   Range("A1").Select

End Sub
VBA Hyperlinks Example 1-1

Step 2: Now, open hyperlinks using the Active Cell object. Add method.

Code:

Sub Hyperlink_Example1()

  Worksheets("Example 1").Select
  Range("A1").Select

  ActiveCell.Hyperlinks.Add(

End Sub
VBA Hyperlinks Example 1-2

Step 3: The first argument is “Anchor,” i.e., in which cell we would link to create the VBA hyperlink. In this case, cell A1 and since we have already selected cell A1 to mention it as “Selection.”

Code:

Sub Hyperlink_Example1()

   Worksheets("Example 1").Select
   Range("A1").Select

   ActiveCell.Hyperlinks.Add(Selection,

End Sub
VBA Hyperlinks Example 1-3

Step 4: We are not creating any address here, so ignore the Address as of now.

Code:

Sub Hyperlink_Example1()

   Worksheets("Example 1").Select
   Range("A1").Select

   ActiveCell.Hyperlinks.Add Anchor:= Selection, Address:="",
 
End Sub
VBA Hyperlinks Example 1-4

Step 5: Next is ‘Sub Address.’ Here, we need to mention which sheet we are referring to and the first cell of that sheet.

Code:

Sub Hyperlink_Example1()

Worksheets("Example 1").Select
Range("A1").Select

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Main Sheet'!A1",

End Sub
VBA Hyperlinks Example 1-5

We have mentioned the sheet name as “Main Sheet.” In that sheet cell address is “A1.”

Step 6: Ignore the screen tip as well. For text to display, mention the sheet name.

Code:

Sub Hyperlink_Example1()

Worksheets("Example 1").Select
Range("A1").Select

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Main Sheet'!A1", TextToDisplay:="Main Sheet"

End Sub
VBA Hyperlinks Example 1-6

Run this code using the F5 key or manually. Then, it will create a hyperlink in cell A1 in the sheet “Example 1.”

VBA Hyperlinks Example 1-7

When you click on the hyperlink “Main Sheet,” it redirects to the main sheet.

VBA Hyperlinks Example 1-8

We have seen creating a VBA hyperlink for one sheet. However, when we have many sheets, it isn’t easy to create a VBA hyperlink for each sheet with the same line of code.

Assume you have 11 worksheets, as shown in the below image.

VBA Hyperlinks Example 2

You want to create a hyperlink for each sheet in the Index sheet using VBA code.

Step 1: Define the variable as a worksheet.

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

End Sub
Example 2-1

Step 2: The first thing is to select the worksheet Index and select cell A1.

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

End Sub
Example 2-2

Step 3: Now, open For Each Loop in VBA.

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

For Each Ws In ActiveWorkbook.Worksheets

Next Ws

End Sub
Example 2-3

Step 4: Since we have selected cell A1, it is now an active cell. So, start the hyperlink with the active cell.

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

For Each Ws In ActiveWorkbook.Worksheets
ActiveCell.Hyperlinks.Add(

Next Ws

End Sub
Example 2-4

Step 5: An anchor is a hyperlink cell. So, it is the active cell.

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

For Each Ws In ActiveWorkbook.Worksheets
ActiveCell.Hyperlinks.Add Anchor:=ActiveCell,

Next Ws

End Sub
VBA Hyperlinks Example 2-5

Step 6: Address is nothing mentioned it as “.

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

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

Next Ws

End Sub
Example 2-6

Step 7: Subaddress: When we loop through the sheet, it should be the sheet name. To refer to the sheet name, we need a single quote, “’” with sheet name and “! Cell Address,” and close the sheet name with a single quote “’.”

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

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

Next Ws

End Sub
VBA Hyperlinks Example 2-7

Step 8: Ignore the screen tip. For text to display, you can enter the worksheet name.

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

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

End Sub
Example 2-8

Step 9: To store the hyperlink of each sheet in a different cell, every time a hyperlink, one must create it for one sheet. We need to move down one cell from the active cell.

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

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

Next Ws

End Sub
Example 2-9

It will create a hyperlink of all the sheets in the Index sheet. This code is dynamic whenever there is any addition or deletion of sheets. Therefore, we need to run this code to have an updated hyperlink.

VBA Hyperlinks Example 2-10

Recommended Articles

This article has been a guide to VBA Hyperlinks. Here, we learn how to create hyperlinks in the worksheet using VBA code to quickly move from one sheet to another, along with some simple to advanced examples. Below are some useful Excel articles related to VBA: -