Hyperlink not triggering macro

0
votes

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.

1
excelvba
It's not clear from the question whether that handler should be in all 6 sheets, or whether it's only on Sheet1. In any case, grabbing any worksheet event handler and moving it into ThisWorkbook can't possibly work - the signature doesn't match, it's not a Workbook event. – Mathieu Guindon
I think it is: Target.Address – Guest
How is the hyperlink created? What does Debug.Print Sheet1.Hyperlinks.Count return? – Mathieu Guindon
@Guest Target is a Hyperlink, not a Range. The Address of a hyperlink pointing to e.g. stackoverflow.com would be stackoverflow.com – Mathieu Guindon
There are different ways of creating clickable hyperlinks, but not all of them create a Hyperlink object in the worksheet's Hyperlinks collection. Without knowing how the hyperlnks are created, this question is unanswerable. – Mathieu Guindon

1 Answers

1
votes

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