VBA Hyperlinks
In this Article
- VBA Hyperlinks
- Add a Hyperlink with VBA
- Adding Text to Display with VBA
- Adding a ScreenTip with VBA
- Delete a Hyperlink with VBA
- Delete all the Hyperlinks in a Worksheet
- Follow a Website Hyperlink Using VBA
- Follow a Hyperlink to a Folder on Your Drive
- Follow a Hyperlink to a File on Your Drive
- Go to a Cell in another sheet in the same Workbook
- Display all the Hyperlinks in a Worksheet
- Display all the Hyperlinks in a Workbook
- Using the FollowHyperlink Method to Create Emails
- Adding a Hyperlink to an AutoShape in Excel
- Inserting the Hyperlink Formula into a Cell Using VBA
- Adding a Hyperlink to a Button in Access
- Creating a Hyperlink from a Selection in Word
This VBA Tutorial covers the different ways to work with hyperlinks in VBA.
VBA Hyperlinks
With VBA, you can add hyperlinks, remove hyperlinks, create emails using hyperlinks, and open files using hyperlinks in VBA.
Add a Hyperlink with VBA
The Hyperlinks.Add Method adds a hyperlink to a cell using VBA.
The following code will add a hyperlink to cell A1:
Sub AddHyperlinkToCell()
ActiveSheet.Hyperlinks.Add Range("A1"), Address:="https://autovbax.com/"
End Sub
The result is:
Adding Text to Display with VBA
You can add neat user-friendly text to display to your hyperlink with VBA. If you used the worksheet functionality you would insert a hyperlink and then add a text to display in the dialog box provided.
The code below demonstrates how to add text to display to your hyperlink using VBA:
Sub TextToDisplayForHyperlink()
ActiveSheet.Hyperlinks.Add Range("A1"), Address:="https://autovbax.com/", TextToDisplay:="Automate Excel"
End Sub
The result is:
Adding a ScreenTip with VBA
You can add a ScreenTip to your hyperlink that the viewer will see when they hover over the link.
The code below shows how to add a ScreenTip to your hyperlink using VBA:
Sub ScreenTipForHyperlink()
ActiveSheet.Hyperlinks.Add Range("A1"), Address:="https://autovbax.com/", TextToDisplay:="Automate Excel", ScreenTip:="This is the link for Automate Excel"
End Sub
The result is:
Delete a Hyperlink with VBA
The Hyperlinks.Delete Method can be used to delete a hyperlink from a cell.
The following code will delete the hyperlink from cell A1 and the text in the cell.
Note that just deleting the hyperlink, does not delete the text itself that’s why if you want the text deleted you also have to use the Clear method.
Sub DeleteHyperlinkinCell()
Range("A1").Hyperlinks.Delete
Range("A1").Clear
End Sub
Delete all the Hyperlinks in a Worksheet
You can also delete all the hyperlinks in your worksheet using the Hyperlinks.Delete method.
The following code will delete all the hyperlinks in the first worksheet in your workbook:
Sub RemoveAllHyperlinksInASheet()
ThisWorkbook.Sheets(1).Hyperlinks.Delete
End Sub
Follow a Website Hyperlink Using VBA
The following code allows you to open a website address, in a new window in your browser using the FollowHyperlink method in your workbook:
Sub FollowHyperlinkForWebsite()
ActiveWorkbook.FollowHyperlink Address:="https://autovbax.com", NewWindow:=True
End Sub
Follow a Hyperlink to a Folder on Your Drive
The following code will open a folder called ExcelFiles on the Desktop using the FollowHyperlink Method:
Sub FollowHyperlinkForFolderOnDrive()
ActiveWorkbook.FollowHyperlink Address:="C:\Desktop\ExcelFiles"
End Sub
Follow a Hyperlink to a File on Your Drive
The following code will open an Excel file called WorkbookOne in the folder called ExcelFiles on the Desktop using the FollowHyperlink Method:
Sub FollowHyperlinkForFile()
ActiveWorkbook.FollowHyperlink Address:="C:\Desktop\ExcelFiles\WorkbookOne.xlsx", NewWindow:=True
End Sub
Go to a Cell in another sheet in the same Workbook
You can add a hyperlink to a cell in one sheet that takes you to another sheet, in the same workbook with VBA. Let’s say you are on Sheet1, cell A1 of your workbook and you want to insert a hyperlink to Sheet2, cell B2 of the same workbook, you can use the SubAddress property to do this.
The following code demonstrates how to do this:
Sub GoToAnotherCellInAnotherSheetInTheSameWorkbook()
ActiveSheet.Hyperlinks.Add Range("A1"), Address:="", SubAddress:="'" & Sheet2.Name & "'!B2", TextToDisplay:="Click Here to Go to Sheet2, cell B2 of the same workbook"
End Sub
Display all the Hyperlinks in a Worksheet
You can access the hyperlinks collection and display all the hyperlinks in your worksheet in the Intermediate window in the VBA Editor. You must first press CTRL+G on your keyboard or go to View>Intermediate Window in the VBE Editor, to view the Intermediate Window.
The following code shows how to view the hyperlinks in your worksheet in the Intermediate Window:
Sub ShowAllTheHyperlinksInTheWorksheet()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)
For Each lnk In ws.Hyperlinks
Debug.Print lnk.Address
Next lnk
End Sub
The results are shown in the Intermediate Window.
Display all the Hyperlinks in a Workbook
You can access the hyperlinks collection, to loop through and display all the hyperlinks in your Workbook in a Message Box.
The following code shows how to do this, and uses a nested loop in order to achieve this:
Sub ShowAllTheHyperlinksInTheWorkbook()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each lnk In ws.Hyperlinks
MsgBox lnk.Address
Next lnk
Next ws
End Sub
Using the FollowHyperlink Method to Create Emails
You can also create emails using the FollowHyperlink Method.
The code below will show you how to create emails using the FollowHyperlink Method in VBA:
Sub SendEmailUsingHyperlink()
Dim msgLink As String
msgLink = "mailto:" & "person@email.com" & "?" & "subject=" & "Hello" & "&" & "body=" & "How are you?"
ActiveWorkbook.FollowHyperlink (msgLink)
End Sub
The result is:
Adding a Hyperlink to an AutoShape in Excel
You can add a hyperlink to an Autoshape in Excel so that when the user clicks on the shape they get taken to a website address.
The following code creates a rounded rectangle, adds the text to the rectangle and adds a hyperlink to the rectangle:
Sub AddingAHyperlinkToAShape()
Dim myShape As Shape
Set myDocument = Worksheets("Sheet1")
Set myShape = myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 100, 100, 90, 30)
With myShape
.TextFrame.Characters.Text = "Automate Excel"
End With
ActiveSheet.Hyperlinks.Add Anchor:=myShape, Address:="https://autovbax.com/"
End Sub
The result is:
Inserting the Hyperlink Formula into a Cell Using VBA
Let’s say you have a post title in cell A4 and a post link in cell B4 as shown in the image below.
A quick reminder of the worksheet Hyperlink formula syntax is:
HYPERLINK(link_location, [friendly_name])
link_location – This is the link to the document, file, place in the workbook or online site.
friendly_name – (Optional) – The text or numeric value that is displayed in the cell.
In cell C4, you want to add the hyperlink with a friendly text to display, now usually you would enter the formula =HYPERLINK(B4,A4) in C4 to get the following:
You could instead use VBA to achieve the same result, and insert this formula in the cell, on Sheet1 with the following code:
Sub InsertHyperlinkFormulaInCell()
ActiveWorkbook.Worksheets("Sheet1").Range("C4").Formula = "=hyperlink(B4,A4)"
End Sub
Adding a Hyperlink to a Button in Access
VBA allows you to work with hyperlinks in Access too. Let’s say we have a button on a form and when the user clicks on that button we want them to be taken to a website. One way that you can use to do this, is through the Application.FollowHyperlink method.
We have our sample form with a button called buttonOne shown below.
The code for this button would be:
Private Sub buttonOne_Click()
Application.FollowHyperlink ("https://autovbax.com/")
End Sub
Creating a Hyperlink from a Selection in Word
You can work with hyperlinks in Word VBA as well.
Let’s say we have text in Word that says “Click Here to Be Taken to the Automate Excel Website”, that is selected as shown below.
To turn this text into a hyperlink using VBA, you can use the following code:
Private Sub TurnASelectionIntoAHyperlink()
ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:="https://autovbax.com/", ScreenTip:="Click Here Please", Target:=NewWindow
End Sub
The result is: