WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Hyperlinks

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 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.

VBA Hyperlink

The formula of VBA Hyperlinks

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

VBA Hyperlink Formula

  • 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?

You can download this VBA Hyperlinks Template here – VBA Hyperlinks Template

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

VBA Hyperlinks 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

VBA Hyperlinks Example 1-1

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

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.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 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

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

VBA Hyperlinks Example 1-6

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”.

VBA Hyperlinks Example 1-7

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

VBA Hyperlinks Example 1-8

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.

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 the 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 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

Example 2-4

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

VBA Hyperlinks Example 2-5

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

Example 2-6

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

VBA Hyperlinks Example 2-7

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

Example 2-8

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

Example 2-9

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.

VBA Hyperlinks Example 2-10

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
29 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Hyperlinks Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More