vba - FollowHyperlink event not working

Question

FollowHyperlink event not FIRE when I use it with hyperlink function :

=IF(C8505=0;IF(F8505=0;"";IF(H8505=0;"Nothing";IF(G8505="Memo";HYPERLINK("\scan2014memo"&H8505&".pdf";" Link ");HYPERLINK("\scan2014"&H8505&".pdf";"Link"))));IF(H8505=0;VLOOKUP("*"&Inbox!$C8505&"*";Sheet1!A:C;1;FALSE);IF(G8505="Memo";HYPERLINK("\scan2014memo"&H8505&".pdf";VLOOKUP("*"&Inbox!$C8505&"*";Sheet1!A:C;1;FALSE));HYPERLINK("\scan2014"&H8505&".pdf";VLOOKUP("*"&Inbox!$C8505&"*";Sheet1!A:C;1;FALSE)))))

My code that I want to fire is :

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
LastR = Sheets("Track").Range("a10000").End(xlUp).Row + 1

With Worksheets("Track")
.Hyperlinks.Add Anchor:=Sheets("Track").Range("a" & LastR), _
Address:="", _
SubAddress:="inbox!" & Target.Parent.Address, _
ScreenTip:=Target.Parent.Address
End With

 End Sub

Answer 1

This is by design. The HYPERLINK() function does not raise the event, Inserted hyperlinks do raise the event. If you need the event to be raised, modify your formula to jump to a transfer cell in the worksheet, like Z100.

In Z100 place an Inserted hyperlink to get to the final destination.

This way to get the flexibility of a formula and the macro capability at the same time.