FollowHyperlink event not working

Question

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

=IF(C8505=0;IF(F8505=0;"";IF(H8505=0;"Nothing";IF(G8505="Memo";HYPERLINK("\\scan2014\memo\"&H8505&".pdf";" Link ");HYPERLINK("\\scan2014\"&H8505&".pdf";"Link"))));IF(H8505=0;VLOOKUP("*"&Inbox!$C8505&"*";Sheet1!A:C;1;FALSE);IF(G8505="Memo";HYPERLINK("\\scan2014\memo\"&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 run 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 an event; the inserted hyperlinks actually raise the event. If you need the event to be raised, change the formula to go to the transfer cell on the sheet, for example Z100.

In the Z100, place the Paste hyperlink to get to your final destination.

This is the way to get formula flexibility and macro capabilities at the same time.