Running Excel Macro On Hyperlink Click

Question

I'm trying to assign macros to hyperlinks I have set up in excel. I have the hyperlinks linking back to the same cell that contains the hyperlink. Based on code examples I've found I've come up with this code block:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Select Case Target.Range.Address

Case "$B$3"
     MsgBox ("Test")
Case "$Z$3"
     MsgBox ("Test")
Case Else
    Exit Sub
End Select

End Sub

Nothing is happening when I click either hyperlink. I have tried running Application.EnableEvents = True as well and still can't get anything to fire. Also, I have verified the code block is set in the correct worksheet module. Any help would be appreciated. Thanks!

EDIT: Here is a screencap of the hyperlink dialog as an example for the hyperlinks I have set up:

I have tried both "Z3" and $Z$3 for the address (both ways direct me to the cell properly, it just doesn't fire the macro.)

just Debug.Print "Ok" doesn't do anything, but if I put only MsgBox ("Test") in the Worksheet_FollowHyperlink event it does work. Also, if I put a call to one of my macros it will trigger. However, I need to be able to call different macros based on different hyperlinks. I tried If statements instead of a Select Case and it doesn't worth that way either. — SincereApathy
what does MsgBox ("Target.SubAddress") tell you? Or what does MsgBox ("Target.Range.Address") show you? — Dirk Reichel

Gary's Student Gary's Student · Accepted Answer · 2016-04-13T01:26:49

Your code works if the code is in the worksheet code area

Right-click the tab at the bottom and:

Running Excel Macro On Hyperlink Click

1 Answers