VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

We can do many things using Hyperlinks in Excel VBA. The following examples will show you how to add or create and remove hyperlinks, how to open files using hyperlinks. And sending emails using hyperlinks in Excel VBA.

Important Methods and properties of Hyperlinks Object:

Add Method:

Add method of hyperlinks will add a hyperlink to a specific range or shape.

Delete Method:

Delete method of hyperlinks will delete the hyperlink.

Count Property:

Count property of hyperlinks will returns number of hyperlinks in object hyperlinks collection.

Important Methods of Hyperlink Object:


AddToFavorites method of workbook will add a shortcut to the workbook or hyperlink to the Favorites folder.


CreateNewDocument method of hyperlink will creates a new document linked to the specified hyperlink.


Follow method of a hyperlink will displays a cached document, if it’s exist. Otherwise will resolve the hyperlink to download the target document and displays the document in the appropriate application.

Important Properties of Hyperlink Object:


Address property of hyperlink object will returns the address of the hyperlink. It can be also used for setting an address value the hyperlink of the target document.


Creator property of hyperlink object will the application in which this object was created.


EmailSubject property of hyperlink object will returns the subject line of the email. You can also use this property to set the subject line of hyperlink email.


Name property of hyperlink object will return name of the object in text format.


Parent property of hyperlink object will return the parent object for the specified object.


Range property of hyperlink object will returns a range where hyperlink is attached.


ScreenTip property of hyperlink object will return the ScreenTip label of a hyperlink.


Shape property of hyperlink object will return a Shape object that represents the shape attached to the hyperlink.


SubAddress property of hyperlink object will return the location of the document associated with the hyperlink.


TextToDisplay property of hyperlink object will return label of the hyperlink. You an also use this property to set the caption to be displayed for the specified hyperlink. Address of the hyperlink. Will be the default label.

Hyperlinks Examples using Excel VBA:

Following are the examples on using Hyperlinks in Excel with VBA.

Add Create Hyperlinks in Excel VBA:

The below example code will show you adding hyperlinks using Excel VBA.

Sub sbCreatingHyperLink()
ActiveSheet.Hyperlinks.Add Range("A5"), ""

End Sub
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert New Module
  4. Copy the above code and Paste in the code window
  5. Press F5 to execute it
  6. You can see a new hyperlink is added at A5

Removing Hyperlinks in Excel VBA:

The below example code will show you removing hyperlinks using Excel VBA.

Sub sbRemovingHyperLink()


End Sub
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert New Module
  4. Copy the above code and Paste in the code window
  5. Press F5 to execute it
  6. It should remove the hyperlink from A5

VBA Open File Folder Website Using FollowHyperlink method in Excel:

The below example code will show you opening files, folders or a specific website using FollowHyperlink in Excel VBA.

Sub sbOpenAnything()
    Dim sXLFile As String
    Dim sFolder As String
    Dim sWebsite As String
    sFolder = "C:Temp" ' You can change as per your requirement
    sXLFile = "C:Temptest1.xls" ' You can change as per your requirement
    sWebsite = "" ' You can change as per your requirement

    ActiveWorkbook.FollowHyperlink Address:=sFolder, NewWindow:=True 'Open Folder
    ActiveWorkbook.FollowHyperlink Address:=sXLFile, NewWindow:=True 'Open excel workbook
    ActiveWorkbook.FollowHyperlink Address:=sWebsite, NewWindow:=True 'Open Website

End Sub
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert New Module
  4. Copy the above code and Paste in the code window
  5. Change the required file, folder and website to open
  6. Press F5 to execute it
  7. It should open the all file, folder and website mentioned in the code

VBA Create Send Emails Using FollowHyperlink Method – Send Keys in Excel:

The below example code will show you how to send an email using FollowHyperlink Method – Send Keys in Excel VBA.

Sub sbCreatingEmail()

    Dim sMsg As String
    Dim Recipient As String
    Dim RecipientCC As String
    Dim RecipientBCC As String
    Dim sSub As String
    Dim sHLink As String
    Recipient = ""
    RecipientCC = ""
    RecipientBCC = ""

    sSub = "Test Mail"
    sMsg = "Hi, this is a auto generated mail from excel"
    sHLink = "mailto:" & Recipient & "?" & "cc=" & RecipientCC & "&" & "bcc=" & RecipientBCC & "&"
    sHLink = sHLink & "subject=" & sSub & "&"
    sHLink = sHLink & "body=" & sMsg

    ActiveWorkbook.FollowHyperlink (sHLink)
    Application.Wait (Now + TimeValue("0:00:03"))
    Application.SendKeys "%s" 'Send Keys

End Sub

VBA to loop through all hyperlinks in a worksheet:

We can use hyperlinks collection of an worksheet to loop through all hyperlinks in a spread sheet. The following example will show you to display all link address of the hyper links in the worksheet.

Sub sbLoopThroughAllLinksinSheet()
'For each link in the worksheet
For Each lnk In Sheets("Sheet1").Hyperlinks
    'display link address
    MsgBox lnk.Address
End Sub

VBA to loop through all hyperlinks in a workbook :

We can use hyperlinks collection of an worksheet to loop through all hyperlinks in a workbook. The following example will show you to display all link address of the hyper links in active workbook by sheet.

Sub sbLoopThroughAllLinksinWorkbook()
'for each sheet in active workbook
For Each sh In ActiveWorkbook.Sheets
    'For each link in a shet
    For Each lnk In sh.Hyperlinks
        'display worksheet name and link address
        MsgBox sh.Name & ":" & lnk.Address
End Sub
Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: April 17, 2013


  1. James March 28, 2014 at 1:03 AM - Reply

    How do I verify many links in Excel 2007?

  2. PNRao March 29, 2014 at 12:24 PM - Reply

    Hi James,
    Please check the examples, I have added the required examples to loop through the hyperlinks in a worksheet or workbook.


  3. Gary May 21, 2014 at 8:42 PM - Reply


    I am trying to hyperlink results from a search utility to the worksheet that the result was found. Is this possible?

    Kind Regards,


  4. PNRao May 25, 2014 at 7:12 PM - Reply

    Hi Gary,
    Yes, it’s possible. You can extract the html page with search results. Then you can loop through the each link in the html Document object.


  5. kyle June 5, 2014 at 4:08 PM - Reply

    how do i create a button to open up different sheets with in my workbook

  6. PNRao June 7, 2014 at 6:00 PM - Reply

    Hi Kyle,
    Please follow the below steps:
    1. Place a button in worksheet: (Go to Insert menu and choose a rectangle shape from shapes group)
    2. Add hyperlink: (Right Click on rectangle shape which is added in above step and click on ‘Hyperlink…’ command in right click menu)
    3. Select required sheet: (Click on the ‘Place in this document’ and select required sheet to link), then Press OK button.

    Hope this help! Thanks-PNRao!

  7. Jonny July 17, 2014 at 3:12 PM - Reply

    Hi PNRao,
    I’m trying to write a code (which i think uses several of these commands) which will open several hyperlinks, which are to online PDFs, and print them. Ideally I would have this on a button for different combinations of links.

    Additionally some of these links, i would only want to print some pages…

    Is this possible?

  8. PNRao July 17, 2014 at 7:38 PM - Reply

    Hi Jonny,

    You can use FollowHyperlink method to open any file.

    To print the PDF file, you can use ShellExecute command as shown below:

    ‘[Code]—–Copy this code and place in a new module

    Public Declare Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” ( _
    ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Sub sbPrintPDFs()
    Dim tempPrint
    Dim strFile As String

    strFile = “C:Test.pdf” ‘ Your PDF File Path
    ‘To pen a PDF
    ‘tempPrint = fnOpenPDF(0, strFile)

    ‘To Print PDF
    tempPrint = fnPrintPDF(0, strFile)
    End Sub

    Public Function fnPrintPDF(lngHw As Long, strFileName As String)
    On Error Resume Next
    Dim X As Long
    X = ShellExecute(lngHw, “Print”, strFileName, 0&, 0&, 3)
    End Function

    Public Function fnOpenPDF(lngHw As Long, strFileName As String)
    On Error Resume Next
    Dim X As Long
    X = ShellExecute(lngHw, “Open”, strFileName, 0&, 0&, 3)
    End Function


    And I am not sure about printing only specific pages!

    Hope this helps! Thanks-PNRao!

  9. Jonny July 18, 2014 at 12:25 PM - Reply

    This comes up with a compile error “expected: string constant”

    and where you have “strFile = “C:Test.pdf” ‘ Your PDF File Path” – Is this assuming my file will already be saved to my hard drive? The files are online and need to stay there in case of changes.

    Any suggestions appreciated!

  10. PNRao July 18, 2014 at 6:55 PM - Reply

    strFile = “C:Test.pdf” is your file name. i.e; you need to replace “C:Test.pdf” with your file name (something like “”)


  11. Gavrav July 24, 2014 at 12:57 PM - Reply

    I am trying to execute query statement in ssms 2005 by automatically clicking that query statement as hyperlink which is in excel. Is it possible to interlink that query statement which is actually written in excel with help of macro or something related to that.

  12. Peter January 10, 2015 at 2:25 AM - Reply

    Thanks for this great resource!
    I have two columns of data: one is a list of article headlines and second is a list of URLs to the articles (headlines and associated URLs are in the same row). I’m trying to merge the two columns to create hyperlinked headlines that link to the appropriate article. This is a function that can be done using the CTRL-K Insert Hyperlink function on a one at a time basis but I’m looking to be able to accomplish this one a larger scale. Any thoughts?

  13. Dana April 17, 2015 at 11:26 PM - Reply

    Hello dear author.
    Please help me to understand Visual basic. I have a command button in excel, which opens my Word file. After i make changes in this Word document i do Save As and change file saving directory. Please, tell me how i can add Hyperlink to the excel automatically AFTER that Word file have been saved to the new directory?
    Thanks a lot.

  14. Kirk May 18, 2015 at 10:16 PM - Reply

    Hi PNRao,
    I tried the code with my master sheet, but it did generate the hyperlinks. I am trying to set up a workbook where future parts can be added (each part will be a new sheet). As I have it now, when the sheet is made and some old data is entered, it takes the name from that data (specifically from J2) to label the sheet. The master sheet already generates the names of the sheets, but I cant get them to hyperlink to each of the respective sheets. Any help on this would be appreciated, thanks.

  15. Dave June 2, 2015 at 3:00 AM - Reply

    I have a spreadsheet with links to documents that have to be opened/copied to another location. Also while opening the file it would need to search the sheet for a keyword or two. Then save the pdf / document to a different location. Is this possible with vba?

  16. phil July 16, 2015 at 6:40 PM - Reply

    Thanks for your thorough examples.
    I want to hide hyperlinks when I print the worksheet.
    My approach is/was to collect all the hyperlinks
    of the worksheet in an array, then set the name in each
    hyperlink to spaces. After printing I will go back and
    restore the hyperlink names from the array.

    Here is my testing code:
    ReDim hArray(1 To ActiveSheet.Hyperlinks.Count) As Variant
    Debug.Print “=.=.=.=.=.=.=.=.=.=.=.=.=.=.=”
    Debug.Print “active worksheet ” & ActiveSheet.Name
    n = 1 ‘hArray is base 1
    Set WorkRng = ActiveSheet.UsedRange ‘range to search = the entire worksheet
    For Each Rng In WorkRng ‘search for hyperlink cells
    If Rng.Hyperlinks.Count > 0 Then ‘this cell refers to a hyperlink
    hArray(n) = Rng.Hyperlinks(1).Name
    Debug.Print n & “___________________________”
    Debug.Print “name in array ” & hArray(n)
    Debug.Print “name before ” & Rng.Hyperlinks(1).Name
    Debug.Print “text before ” & Rng.Hyperlinks(1).TextToDisplay

    Rng.Hyperlinks(1).TextToDisplay = ”
    Debug.Print “text after ” & Rng.Hyperlinks(1).TextToDisplay
    ‘Rng.Hyperlinks(1).Name = ”
    ‘Debug.Print “name after ” & Rng.Hyperlinks(1).Name
    n = n + 1
    End If

    Debug output:
    active worksheet Sheet1
    name in array Auto(1)
    name before Auto(1)
    text before Auto(1)
    text after Auto(1)
    name in array Auto(2)
    name before Auto(2)
    text before Auto(2)
    text after Auto(2)
    name in array Condo Declarations
    name before Condo Declarations
    text before Condo Declarations
    text after Condo Declarations
    name in array Payment
    name before Payment
    text before Payment
    text after Payment
    name in array Invoice
    name before Invoice
    text before Invoice
    text after Invoice

    1. Notice that Rng.Hyperlinks(1).Name = ” is commented out. When
    I un-comment that I get “wrong number of arguments or invalid property assignment”
    2. Although Rng.Hyperlinks(1).TextToDisplay = ” does not abort, the
    hyperlink label is not actually cleared..

  17. PNRao July 16, 2015 at 10:42 PM - Reply

    Hi Phil,
    The easiest way to hide all links is to change its cell format, please find the code here:


  18. Allen September 21, 2015 at 3:11 PM - Reply

    Hi PNRao. For example I have a spreadsheet with 2 columns – one is the display text in column A and the URL address in column B. How do I create a macro so that it will recursively update all my cells in column A with the URL address that’s found in column B?



  19. Zahar October 16, 2015 at 9:55 AM - Reply

    Hi PNRao.I need to print PDF from hyperlink from specific cell (e.g cell A2). is it possible?. your code will retrieve PDF from outside excel (e.g hard disk or web)

  20. Partha December 29, 2015 at 11:17 AM - Reply

    Dear Mr. Rao

    Is it possible to create a hyperlink with username & password to open a password protected site in a single click from excel?



  21. Giorgio Tassi December 30, 2015 at 5:05 PM - Reply

    Good morning, I have tried to sendi an e-mai of a worksheet following your instructions. I got to the stage where the e-mail is ready, with the correct addresses and subject text and attaching text. However no enclosure attached and a message advising that “Application.SendKeys “%s” ‘Send Keys’ is not running in a Macintosh platform. Can you pls advise the correction to adjust the instruction for a Mac with OS X El Capitan version 10.11.1 using Excel 2011 for Mac . Many thanks and best regards from Italy.

    ActiveWorkbook.FollowHyperlink (sHLink)
    Application.Wait (Now + TimeValue(“0:00:03”))
    Application.SendKeys “%s” ‘Send Keys

  22. Ramaiah Ganta March 24, 2016 at 1:04 AM - Reply

    How we can give the hyperlink for respective folder’s files name in active workbook

  23. mary October 6, 2016 at 11:31 PM - Reply

    How do I write the code in visual basic for excel to automatically check my hyperlinks to make sure they are good upon opening my excel sheet. And if the links are broken how to find the file . Some times my file names get changed like by date or revisions

  24. Amol October 17, 2016 at 10:25 PM - Reply

    I have multiple sheets in my excel and I want to add hyperlink to each sheet and hide it. How can I do it with VBA?

  25. Arun March 14, 2018 at 7:22 PM - Reply

    How we open a hyperlinks in VBA. step by step

Leave A Comment