Run Macro When Hyperlink Clicked Issue

[t0ny84]

Hi,

I am hoping someone could point me in the right direction, I am trying to get a macro to run when a hyperlink is clicked.
The below VBA code works when I use Insert Hyperlink in Excel but not when I use the =HYPERLINK function in a formula.

Code To Call Macro When Link Clicked - I have tried both Workbook_Sheet and Worksheet_ but neither work with =HYPERLINK

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
If Target.Parent.Address = "$A$1" Then
    Call Hello   'test macro
End If
End Sub

Below is an example function, ideally if A1 = 5 then - else show a hyperlink to another sheet within the document else show -.

=IF(A1="5","-", HYPERLINK("#"&"'"&MONTH($E$3)&"'!E19","…more tasks found."))

When I don't have the macro in the editor the link works and takes me to the relevant sheet, when I have the code in the editor it doesn't do anything.

Hopefully this makes sense and thanks in advance!
t0ny84

 

[t0ny84]

If example workbook would help let me know and I can upload one when I am next at work.

 

[t0ny84]

In case anyone else has this issue:

=HYPERLINK function doesn't work with SheetFollowHyperlink

I figured out the following work around of adding a blank hyperlink to cell referencing itself then adding the formula into the cell.

Sub createhyperlinks()
'This script simplifies adding they hyperlink, to use select cells then click run.
Dim c As Range
Set c = Selection
         
For Each cell In c
    ActiveSheet.Hyperlinks.Add cell, Address:="#"
        Next
End Sub

Once the blank hyperlink has been added enter your formula into the cell(s) then paste the below code onto ThisWorkbook (or edit for the worksheet option).

e,g, =if(a1=a2,"False","Positive")

Private Sub Workbook_SheetFollowHyperlink(ByVal sh As Object, ByVal Target As Hyperlink)
  Application.ScreenUpdating = False
  If ActiveCell.Value = "Positive"
     Call hypelink   'This is the name of my macro I wanted called.
Else: Exit Sub

End If
Application.ScreenUpdating = True
End Sub