Hyperlinks in Excel VBA – Explained with Examples!

Home/Excel VBA/Hyperlinks in Excel VBA – Explained with Examples!

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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:

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

CreateNewDocument:

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

Follow:

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:

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:

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

EmailSubject:

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:

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

Parent:

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

Range:

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

ScreenTip:

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

Shape:

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

SubAddress:

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

TextToDisplay:

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.

Code:
Sub sbCreatingHyperLink()
ActiveSheet.Hyperlinks.Add Range("A5"), "http://mongopono.ru"
End Sub
Instructions:
  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.

Code:
Sub sbRemovingHyperLink()
Range("A5").Hyperlinks.Delete
End Sub
Instructions:
  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.

Code:
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:\Temp\test1.xls" ' You can change as per your requirement
sWebsite = "http://mongopono.ru/" ' 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
Instructions:
  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.

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

Code:
Sub sbLoopThroughAllLinksinSheet()
'For each link in the worksheet
For Each lnk In Sheets("Sheet1").Hyperlinks
'display link address
MsgBox lnk.Address
Next
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.

Code:
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
Next
Next
End Sub
LIMITED TIME OFFER
By |April 17th, 2013|Excel VBA|24 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

24 Comments

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

    How do I verify many links in Excel 2007?

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

      Thanks-PNRo!

    • 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

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

    Hi,

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

    Kind Regards,

    Gary

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

      Thanks-PNRao!

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

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

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

  4. 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?
    Thanks.

    • 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

      ‘[/Code]

      And I am not sure about printing only specific pages!

      Hope this helps! Thanks-PNRao!

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

        Thanks,
        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!

        • 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 “website/filename.pdf”)

          Thanks-PNRao!

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

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

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

    Hi-
    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?
    thanks!
    -Peter

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

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

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

    Hello,
    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?

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

    Debug output:
    =.=.=.=.=.=.=.=.=.=.=.=.=.=.=
    active worksheet Sheet1
    1___________________________
    name in array Auto(1)
    name before Auto(1)
    text before Auto(1)
    text after Auto(1)
    2___________________________
    name in array Auto(2)
    name before Auto(2)
    text before Auto(2)
    text after Auto(2)
    3___________________________
    name in array Condo Declarations
    name before Condo Declarations
    text before Condo Declarations
    text after Condo Declarations
    4___________________________
    name in array Payment
    name before Payment
    text before Payment
    text after Payment
    5___________________________
    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..

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

    Thanks.

    Allen

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

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

    Regards

    Partha

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

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

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

Leave A Comment


Related pages


if formula in excel 2007 with examplevlookup function in vbahow to add a drop down list in excel 2007sql select from excel sheetadd developer tab excel 2013excel len functionfind duplicates in excellprotect sheet excelexcel 2007 drop down list multiple selectionexcel sales dashboardhow to create a checkbox in excelvba wordhow to use the sumif function in microsoft excel 2010show userformexcel copy cell contentshow to open excel vbahow to protect sheets in excelmacro to copy and pastevba global variableexcel blank cell valuemsgbox promptslicer excel 2010 tutorialhow to learn excel vbacommand button vba excelvba dynamic arraysremove hyperlinks in excel 2007groupby and orderby in sqlhow to use a sumifsql tutorial for experiencedexcel colorindexexcel vba compare two columns different worksheetsvb6 check if file existsclustered cylinder chart excelcountif in vbaoffset formula in excel with examplerange cell vbaworksheet or workbookvba paste valuetextbox controlvba excel redim preservedatediff in vbawriting vba macrosqlikview beginners guidehow to lock a spreadsheetcombining worksheets in excelhighlight column excelexcel isna vlookupvba case elsesteps for vlookupexcel vba activecell.addressddl dml commandsexcel logical operatorhow to create pivot table in excel 2003pivot chart excel 2007microsoft word 2010 assessment test answershow to enable macro in excelexcel and word tests for interviewswot analysis excel templateexcel if isblank thenexample of lookup in excelcoding in vbahow to unlock the excel fileexcel macro open foldermacro in excel tutorial pdfcolor excel cellshow we use vlookup in excelnested vlookuphyperlink to file in excelsql select from excel sheetapplication.getopenfilename vbaexcel easy vba