Excel VBA Hyperlinks – Linking, Following, and Managing

With Excel VBA, you can create hyperlinks that allow users to navigate effortlessly to specific locations within the same worksheet, other worksheets, or websites. Additionally, you can manage the hyperlinks, such as displaying them on the Immediate window or removing them from the worksheet or workbook.

This tutorial shows how to create, follow, and manage hyperlinks.

Create a Hyperlink to Another Cell in the Same Worksheet Using Excel VBA

If you want to create a link in, say, cell A1 to cell B5 in the same worksheet, here’s how you can do it:

1. Press Alt + F11 to open the Visual Basic Editor. Alternatively, you can click Developer >> Code >> Visual Basic.

2. On the Visual Basic Editor, click Insert >> Module to insert a module.

3. Type the following sub-procedure in the module:

4. Click anywhere in the sub-procedure and press F5 to run the code.

The code ran and added the hyperlink and text to display to cell A1 as shown below:

When you hover your cursor over the hyperlink, a hand-shaped icon appears, with a screen tip indicating that the link is to cell B5.

When you click the hyperlink once, the cell selector moves to cell B5.

Explanation of the Code

The code sets up a worksheet reference, selects a cell, and then adds a hyperlink to that cell, linking it to another cell within the same worksheet.

Here’s a breakdown of the code:

1. Dim ws As Worksheet and Dim cell As Range: These statements declare two variables, ws and cell, of types Worksheet and Range, respectively. The ws variable will be used to reference the worksheet, and the cell variable will store the range of the cell to which the hyperlink will be added.

2. Set ws = ThisWorkbook.Worksheets("Sheet2"): This statement sets the ws variable to reference a specific worksheet named “Sheet2” within the current workbook.

3. Set cell = ws.Range("A1"): This statement sets the cell variable to refer to cell A1 in the worksheet referenced by ws.

4. ws.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="Sheet2!B5", TextToDisplay:="Click Here To Go To cell B5": This statement creates a hyperlink in the worksheet. It uses the Hyperlinks collection of the worksheet referenced by ws to add a new hyperlink. The Anchor parameter specifies the cell (referenced by the cell variable) to which the hyperlink will be attached.

Since the hyperlink is within the same worksheet, the Address parameter is set to an empty string (“”). The SubAddress parameter specifies the target cell where the hyperlink will navigate. In this example, it is set to “Sheet2!B5”, which means the hyperlink will take you to cell B5 in “Sheet2”.

The TextToDisplay parameter specifies the text displayed as the hyperlink. In this example, it is set to “Click Here To Go To cell B5”.

Make a Hyperlink to Another Worksheet in the Same Workbook Using Excel VBA

If you want to make a link in, say, cell A1 in “Sheet3” to cell B5 in “Sheet4” of the same workbook, here’s how you can do it:

1. Open the Visual Basic Editor and insert a module as explained in the previous section, ” Create a Hyperlink to Another Cell in the Same Worksheet.”
2. Type the following sub-procedure in the module:

3. Click inside the procedure and press F5 to run the code.
4. Press Alt + F11 to switch to the current worksheet.

The code executed and inserted a hyperlink and text to display into cell A1 of “Sheet3” as shown below:

Clicking the link will select cell B5 in “Sheet4”.

Create a Hyperlink to a Website Using Excel VBA

If you want to create a link to a website like microsoft.com in cell A1 of your worksheet, here’s how you can do it:

1. Open the Visual Basic Editor and insert a module as explained in the section ” Create a Hyperlink to Another Cell in the Same Worksheet.”
2. Type the following subprocedure in the module:

3. Press F5 to run the code.
4. Press Alt + F11 to switch to the active worksheet.

The code inserted a hyperlink into cell A1 as shown below:

A hand-shaped icon will appear when you move your cursor over the hyperlink. This icon signals you can click once to follow the link.

Clicking the hyperlink opens the www.microsoft.com website.

Create a Hyperlink to a Folder on Your Drive Using Excel VBA

You can use the following steps to create a hyperlink to open a folder on your local drive:

1. Open the Visual Basic Editor and insert a module as explained in the section ” Create a Hyperlink to Another Cell in the Same Worksheet.”
2. Type the following sub-procedure in the module:

3. Click anywhere in the code and press F5 to run the code.

The code executes and opens the “Excel Tutorials” folder on the C drive.

Explanation of the Code

The code opens Windows Explorer and displays the contents of the “C:\Excel Tutorials” folder, assuming the folder exists at the specified path.

Here’s how the main statement of the code works:

When the main statement “ActiveWorkbook.FollowHyperlink Address:=”C:\Excel Tutorials” is executed; it opens the Windows Explorer and navigates to the specified folder path, “C:\Excel Tutorials.”

• “ActiveWorkbook” refers to the active workbook in the Excel file.
• “FollowHyperlink” is a method that allows you to open a hyperlink in the default program associated with the specified file or location.
• “Address:=” is a named argument used to specify the address or location of the hyperlink. In this case, it is set to “C:\Excel Tutorials,” which is the path to the local folder.

Make a Hyperlink to a File on Your Drive Using Excel VBA

You can use the following steps to create a hyperlink to open a folder on your local drive:

1. Open the Visual Basic Editor and insert a module as explained in the section ” Create a Hyperlink to Another Cell in the Same Worksheet.”
2. Enter the following code into the module:

3. Click anywhere in the code and press F5 to run the code.

The code executes and opens the “Group Data in Excel Chart” file located in the “Excel Tutorials” folder on the C drive.

Generate and Add a Hyperlink to a Shape Using Excel VBA

In Excel VBA, it is possible to create a sub-procedure that generates a shape and attaches a hyperlink to it. This process allows the user to click on the shape and be redirected to, for example, a website address.

You can use the below steps:

1. Open the Visual Basic Editor and insert a module as explained in the section ” Create a Hyperlink to Another Cell in the Same Worksheet.”
2. Enter the following code into the module:

3. Click anywhere in the code and press F5 to run the code.
4. Press Alt + F11 to switch to the active worksheet.

When you click the shape created by the code, you are directed to the website address shown on the screen tip.

Explanation of the Code

The Excel VBA code creates a rounded rectangle shape in Excel and adds a hyperlink to it. Here’s a breakdown of what each line of code does:

1. Dim myDocument As Worksheet: Declares a variable named myDocument of type Worksheet.

2. Dim myShape As Shape: Declares a variable named myShape of type Shape.

3. Set myDocument = Worksheets("Sheet5"): Assigns the worksheet named “Sheet5” to the myDocument variable.

4. Set myShape = myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 50, 50, 75, 25): Adds a rounded rectangle shape to the worksheet referenced by myDocument. The parameters passed to the AddShape method specify the shape type (msoShapeRoundedRectangle) and the position and size of the shape (50 units from the left, 50 units from the top, 75 units wide, and 25 units tall).

5. With myShape: Starts a code block that allows you to manipulate the properties of the myShape object.

6. .TextFrame.Characters.Text = "Microsoft": Sets the text inside the shape to “Microsoft.” The TextFrame property represents the text formatting properties of the shape, and Characters.Text refers to the actual text content.

7. End With: Ends the code block for manipulating the myShape object.

8. ActiveSheet.Hyperlinks.Add Anchor:=myShape, Address:="https://www.microsoft.com/en-us": Creates a hyperlink on the active sheet. The Anchor parameter specifies the shape (myShape) attached to the hyperlink. The Address parameter sets the URL (“https://www.microsoft.com/en-us”) that the hyperlink points to.

Managing Hyperlinks in Excel

You can display all the hyperlinks in a worksheet or workbook in the Immediate window if you want to review them. Alternatively, you can remove the hyperlinks if you no longer need them.

Display All Hyperlinks in a Worksheet in the Immediate Window

You can use the below steps to display all the hyperlinks in a worksheet in the Immediate window:

1. Open the Visual Basic Editor and insert a module as explained in the section ” Create a Hyperlink to Another Cell in the Same Worksheet.”
2. Enter the following code into the module:

3. Click anywhere in the code and press F5 to run the code.

The code executes and displays the hyperlinks in the worksheet on the Immediate window as shown below:

Note: If you do not see the Immediate window, activate it by pressing Ctrl + G.

Display All Hyperlinks in a Workbook on the Immediate Window

To display all the hyperlinks in a workbook on the Immediate window, follow these steps:

1. Open the Visual Basic Editor and insert a module as explained in the section ” Create a Hyperlink to Another Cell in the Same Worksheet.”
2. Enter the following code into the module:

3. Click anywhere in the code and press F5 to run the code.

All the hyperlinks in the workbook as displayed on the Immediate window as shown below:

Delete All Hyperlinks in a Worksheet or Workbook

If you no longer need hyperlinks in your worksheet or workbook, you can remove them using the steps below:

1. Open the Visual Basic Editor and insert a module as explained in the section ” Create a Hyperlink to Another Cell in the Same Worksheet.”
2. Enter the following code into the module (this code removes all hyperlinks from a worksheet):

Alternatively, enter the following code if you want tot to remove all the hyperlinks from the workbook:

3. Click anywhere in the code and press F5 to run the code.

The code executes and removes all hyperlinks from the worksheet or workbook.

Conclusion

This tutorial showed how to create, follow and manage hyperlinks using Excel VBA. We hope you found the tutorial helpful.