VBA Hyperlinks

Associated Files Download Links

VBA Hyperlinks

VBA allows one to work with hyperlinks in many ways. You can add hyperlinks, remove hyperlinks, create emails using hyperlinks, and open files using hyperlinks in VBA.

This VBA Tutorial covers the different ways to work with hyperlinks in VBA.

Add a Hyperlink with VBA

The Hyperlinks.Add Method can add a hyperlink to a cell using VBA. This is what you could do instead of typing it directly into your cell.

The following code will add a hyperlink to cell A1:

The result is:

Add a Hyperlink to a Cell with VBA

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:

The result is:

Add Text To Display Using VBA

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:

The result is:

Add a ScreenTip to the Hyperlink Using VBA

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.

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:

Follow a Website Hyperlink Using VBA

The following code will allow you to open a website address, in a new window in your browser using the FollowHyperlink method in your workbook:

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:

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:

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:

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:

The results are shown in the Intermediate Window.

Show all Hyperlinks in the Intermediate Window Using VBA

Display all the Hyperlinks in a Workbook

You can access the hyperlinks collection and display all the hyperlinks in your workbook using a Message Box.

The following code shows how to do this, and uses a nested loop in order to achieve this:

You can read more about For Loops in this tutorial on our site.

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:

The result is:

Using Hyperlinks to Create Emails in VBA

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:

The result is:
Adding a Hyperlink to An Autoshape in VBA

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.

Inserting a Hyperlink Using a Formula with VBA

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:

Using the Hyperlink Worksheet Formula in VBA

You could instead use VBA to achieve the same result, and insert this formula in the cell, on Sheet1 with the following code:

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.

Adding a Hyperlink in Access Using VBA

The code for this button would be:

Creating a Hyperlink from a Selection in Word

You can work with hyperlinks in VBA in Word too.
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.

Adding a Hyperlink using VBA in Word

To turn this text into a hyperlink using VBA, you can use the following code:

The result is:

Adding a Hyperlink to a Selected Text in Word with VBA

[yuzo]