VBA Hyperlinks

Hyperlinks are URL attached to a value which is seen when we hover mouse over it and when we click on it the URL is opened, in VBA we have an inbuilt property to create hyperlinks in VBA and to use this property we use Add method together with the hyperlink statement to insert a hyperlink in a cell.

Hyperlinks in Excel VBA

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

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

Formula of VBA Hyperlinks

Let’s look at the formula of the hyperlinks in Excel VBA.

• Anchor: In which cell you would like to create a hyperlink.
• Address: What is the URL to the hyperlink to navigate?
• [Sub Address]: What is the location of the page?
• [Screen Tip]: What is the value to be showed when you place a mouse pointer on the hyperlink name or cell?
• [Text to Display]: What is the test to be displayed in the cell? For example Worksheet Name.

How to Create Hyperlinks in Excel VBA?

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

In the worksheet “Example 1” and in the cell A1, I am going to create the hyperlink using Code in VBA.

Step 1: First select the cell A1 of worksheet Example 1.

Code:

Sub Hyperlink_Example1()

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

End Sub

Step 2: Now by using Active Cell object open hyperlinks. add method.

Code:

Sub Hyperlink_Example1()

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

  ActiveCell.Hyperlinks.Add(

End Sub

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

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

Code:

Sub Hyperlink_Example1()

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

   ActiveCell.Hyperlinks.Add Anchor:= Selection, Address:="",
 
End Sub

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

I have mentioned the sheet name as “Main Sheet” and in that sheet cell address is “A1”.

Step 6: Ignore 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

Ok, done run this code using F5 key or manually then, it will create a hyperlink in the cell A1 in the sheet “Example 1”.

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

Hyperlinks of Multiple Sheets with Loops

We have seen creating a VBA hyperlink for one sheet. When we have many sheets it is difficult to create a VBA hyperlink for each sheet with the same line of code for each sheet.

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

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

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

Code:

Sub Create_Hyperlink()

Dim Ws As Worksheet

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

End Sub

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

Step 4: Since we have already selected the 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

Step 5: 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

Step 6: Address is nothing mention 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

Step 7: Subaddress is when we loop through the sheet it should be the sheet name. To refer 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

Step 8: Ignore Screen tip and 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

Step 9: To store the hyperlink of each sheet in a different cell every time hyperlink created 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

This 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 we just need to run this code to have an updated hyperlink.

Recommended Articles

This has been a guide to VBA Hyperlinks. Here we learn how to create hyperlinks in Worksheet Using VBA Code to quickly move from one sheet to another sheet along with some simple to advanced examples. Below are some useful excel articles related to VBA –

• Excel Find Links
• VBA Today
• Hyperlink Formula in Excel
• Concatenate in VBA