Excel, ThisAddIn.vb: why is Application.SheetFollowHyperlink not called for =HYPERLINK()

Question

I'm trying to detect clicked hyperlinks in Excel. The Application.SheetFollowHyperlink Event claims that it will be called "when you click any hyperlink in Microsoft Excel."

However, although it does fire when a cell contains a url like www.google.com, it does not when the cell contains =HYPERLINK("http://www.google.com", "google").

How do I detect clicks to the second type of hyperlink?

For example, a simple excel add in:

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

    End Sub

    Private Sub Application_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Microsoft.Office.Interop.Excel.Hyperlink) Handles Application.SheetFollowHyperlink
        MsgBox("Hyperlink Clicked")
    End Sub

A message box containing "Hyperlink Clicked" will appear when you click a cell containing www.google.com but not a cell containing =HYPERLINK("http://www.google.com", "google").

Perhaps this will be somehow helpful: http://stackoverflow.com/a/14423084/1953175 — Peter L., Jan 29 '13 at 04:46
Thanks @PeterL. that does look like the same problem. So given that this looks like a bug/feature in excel, can anyone suggest a workaround? — ForeverWintr, Jan 29 '13 at 16:43
From where I sit the only way to use `Application.SheetFollowHyperlink` is to convert `HYPERLINK` links to usual ones using the code similar to available via my above link. — Peter L., Jan 29 '13 at 16:56
@PeterL. So this is weird. I installed your macro, generated my hyperlinks (my vb.net add in generates inter-sheet hyperlinks), and `Application_SheetFollowHyperlink()` is called. Now I uninstall your macro, restart excel, reboot my machine, clean/rebuild my vb.net project, regenerate my hyperlinks, and 'Application_SheetFollowHyperlink' is STILL called. I don't get it. On a separate machine that has never had your macro installed, `Application_SheetFollowHyperlink` still doesn't work. — ForeverWintr, Jan 29 '13 at 18:53
sorry pal, I'm not familiar with any other programming except MS Office VBA. — Peter L., Jan 29 '13 at 18:56

score 0 · Accepted Answer · answered Feb 01 '13 at 19:50

I seem to have solved the issue, although I'm not sure why the following works while the code I used above doesn't:

Private HyperlinkFollower As Excel.DocEvents_FollowHyperlinkEventHandler

Private Sub CalledWhenHyperlinkClicked(ByVal Target As Excel.Hyperlink)
    Dim w As Microsoft.Office.Interop.Excel.Window = Globals.ThisAddIn.Application.ActiveWindow
    MsgBox("hyperlink clicked")
End Sub

EventDel_HyperlinkFollower = New Excel.DocEvents_FollowHyperlinkEventHandler(AddressOf CalledWhenHyperlinkClicked)

AddHandler worksheet.FollowHyperlink, EventDel_HyperlinkFollower

Excel, ThisAddIn.vb: why is Application.SheetFollowHyperlink not called for =HYPERLINK()

1 Answers1

Linked