Execute macro when clicked on hyperlink

Question

I have a worksheet where each row of the data table has a formula based hyperlink. I want those hyperlinks to execute a macro every time one of those is clicked. The macro needs to a parameter to act differently depending on which row's hyperlink is clicked, so for that I am using active cell's row number.

I have tried various ways I found but none of them complete the functionality. In most cases I need to specify the cell address in the event macro itself which is not a scalable option.

Sample Data:

Col A  Col B
A      Link: A
B      Link: B
C      Link: C
D      Link: D
E      Link: E

The second column in the above table should be a list of hyperlinks.

The hyperlink formula I am using in the second column (e.g. in cell B1):

=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename")),FIND("]",CELL("filename"))-FIND("[",CELL("filename"))+1)&ADDRESS(ROW(),COLUMN()-1),"Link: "&$A1)

The macro I am using in the worksheet code:

Private Sub Workbook_SheetFollowHyperlink(ByVal Target As Hyperlink)
    Dim sData As String
    sData = "text: " & sData & Range(Target.Range.Address). _
    Offset(0, -1).Value & vbCr
    MsgBox sData
End Sub

For me in this case, the above macro is not even running when I click on the hyperlink

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2016-06-13T08:22:33

In Excel 2013 - the method 'signature' for the Workbook_SheetFollowHyperlink has an additional argument to your event handler. Can you include the ByVal Sh As Object in your code and try again:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
  'your code goes here
End Sub

Execute macro when clicked on hyperlink

1 Answers