EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VBA Hyperlink

By Dheeraj VaidyaDheeraj Vaidya

Home » VBA » Blog » VBA Lookup & Reference Functions » VBA Hyperlink

VBA Hyperlink

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

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

VBA Hyperlink 1

Select the add method from the list

VBA Hyperlink 2

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

VBA Hyperlink Example 1-1

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

Code:

Private Sub hyper()

ActiveCell.Hyperlinks.Add(

End Sub

VBA Hyperlink Example 1-2

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.

Result of Example 1-3

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.

VBA Hyperlink Example 1-4

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.

Two Worksheets

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

VBA Hyperlink Example 2-2

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

VBA Hyperlink Example 2-3

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

VBA Hyperlink Example 2-4

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.

Result of Example 2-5

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

Result of Example 2-6

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

Multiple Worksheets

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

VBA Hyperlink Example 3-2

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

VBA Hyperlink Example 3-3

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

VBA Hyperlink Example 3-4

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

VBA Hyperlink Example 3-5

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

VBA Hyperlink Example 3-6

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

VBA Hyperlink Example 3-7

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

VBA Hyperlink Example 3-8

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.

Result of Example 3-9

Things to Remember

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

Recommended Articles

This is 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 practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Loops
  2. VBA RGB
  3. VBA Break for Loop
  4. VBA XML
1 Shares
Share
Tweet
Share
Primary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
    • Compare Two Columns in Excel using VLOOKUP
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Independence Day Offer - Become a VBA Developer Learn More