Run a Macro by clicking a Hyperlink in Excel

There are many ways you can run or execute a macro like, pressing the F5 key in VBA or simply clicking the run button. Or, you can insert a button (an ActiveX Control) on your Excel worksheet, click the button and execute the macro. In addition, you can run or trigger VBA macros by clicking the hyperlinks in your worksheet. I’ll show you how.

Usually, web pages have hyperlinks , to switch or jump to another page. We can insert hyperlinks in our Excel worksheet to navigate or switch to a web page or a website, or perform other tasks like sending emails etc. You can insert hyperlinks dynamically to an Excel worksheet using a macro or simply insert it manually using Excel features.

However, sometimes we might need to perform some functions before navigating to a web page or a website. And, to do this we need to capture the click event of an Hyperlink to run a macro or a function. So, let’s see how we can run a macro by clicking a hyperlink in Excel.

First, add a hyperlink to the worksheet.

1) From the top menu in your Excel worksheet, click the Insert tab. Find Hyperlink and click it. It will open the Insert Hyperlink window.

Insert hyperlink in Excel

2) In the Insert Hyperlink window, click Place in This Document option.

   a) Enter any text in Text to Display textbox.
   b) In the Type the cell reference textbox, type the cell where you want to insert the hyperlink. For example D2
   c) Click the Ok button.

Insert hyperlink in a particular cell in Excel

👉 You may also like this: How to send email from Excel to multiple recipients using VBA and Outlook

The Macro

Now, that you have inserted the hyperlinks, let’s see how to handle the click event and run a macro.

In your VBA editor, open Sheet1 from Project Explorer. Choose Worksheet and select the event FollowHyperlink from the events dropdown.

FollowHyperLink evnet in VBA

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    
    Debug.Print Target.Range.row                ' get the row id.
    Debug.Print Cells(Target.Range.row, 3)      ' get the email address.
    
End Sub

The macro above is not doing much, except its showing some data in the debug window like the row id in which the hyperlink is clicked and using the row id its showing an email address.

The Target.Range.row method returns the row id of the link that is clicked. There’s another method that you can use to know the hyperlinks address.

Target.Range.Address

You can use this method as a condition. For example,

If Target.Range.Address = "$F$2" Then
    ' perform some task...
End If

👉 If you want to see an advanced example using the above technique, you can check out this post

← PreviousNext →