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.
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.
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.
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.
You can use this method as a condition. For example,
If Target.Range.Address = "$F$2" Then ' perform some task... End If
Thanks for reading. ☺