Need to intercept hyperlink click in Word and Excel using VBA

[heidi33]

This topic was discussed before n thread707-764402 and a work-around of using a macro button in word was suggested.

I find it hard to believe that you CAN intercept the hyperlink's context menu "Open Hyperlink" option, BUT NOT the "short cut" of clicking (or Ctrl + click) on the hyperlink.

In Word, you can intercept the hyperlink's context menu "Open Hyperlink" item with the following:
Sub HyperlinkOpen() 'function has to have this name -- you are overriding a "built in" function
' *** Add code to do the things you need to do before you go off to the hyperlink
Selection.Hyperlinks(1).Follow AddHistory:=True 'invokes the hyperlink
End Sub


In Excel you do it this way:
In the place where you create the hyperlink, you set the "ONAction" for the hyperlink's "Open Hyperlink" menu item to your function
The code looks like this:
Dim commandBarControl As CommandBarControl
Set commandBarControl = CommandBars.FindControl(ID:= _
CommandBars("Cell").Controls("&Open Hyperlink").ID)
commandBarControl.OnAction = "NewOpenHyperlink" 'This is the name of the function you write

Then you write you own Open Link code similar to this:

Sub NewOpenHyperlink()
' *** Add code to do the things you need to do before you go off to the hyperlink
Selection.Hyperlinks(1).Follow AddHistory:=True 'Call the hyperlink
End Sub


Why doesn't the click go to the same functions? Is is really not possible to intercept the click? I would much appreciate anyone's help.

If it isn't possible to intercept the click, what would be the equivalent solution in Excel to the Word macrobutton solution posted in thread707-764402? I need these "hyperlinks" to look and behave the same way in both Word and Excel.

Thanks for your help,
heidi33