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 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 create hyperlinks in the worksheet to move from one sheet to another sheet quickly, 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.
The 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 will 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.”
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
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 the 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 isn’t easy 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: The 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 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
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 a hyperlink is 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 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 –
- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion