Hyperlink not triggering macro

Question

I'm trying to get all hyperlinks with a specific target range to activate a routine. Although the hyperlinks themselves work fine (target address range ("A1") is selected when I click on the hyperlinks), my routine is not being triggered when I click on them.

The code below is in the "Sheet1" module of my workbook and the hyperlinks target range "A1" within the "Sheet1" worksheet. There are a total of 5 other sheets in the workbook.

I'm really stumped as to why the code is failing to trigger. Any help would be much appreciated!

• Checked the hyperlinks themselves were working and targeting a range on the same worksheet the routine is linked to.
• Removed the "call macro" part of the code and replaced it with a simple message box to check if the routine is being triggered at all.
• Tried placing the routine in the "ThisWorkbook" module instead - this also had no results.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$A$1" Then
        MsgBox ("Yay")
    End If
End Sub

The routine above should be triggered after clicking on the hyperlinks with a target range of "A1", meaning that the message box text should appear to the user.

Answer 1

SubAddress vs Sheet1!

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    If Target.Range.Hyperlinks(1).SubAddress = "Sheet1!A1" Then

        MsgBox ("Yay")
    End If
End Sub