How do I use the FollowHyperlink worksheet event to recognize a hyperlink within a shape?-CodePudding

I've got two buttons on a worksheet that I've named "RemoveButton" and "AddButton". I've also added hyperlinks to both shapes and both shapes will point to the same cell once clicked. When I click both buttons, they point to cell A1 as expected, but the FollowHyperlink code does not recognize that a hyperlink has been clicked.

I wanted to use the FollowHyperlink worksheet event to recognize the shape that is clicked. I created the macro as below:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Debug.Print "Clicked!"
End Sub

When clicking on the shapes, they just point to A1 and "Clicked!" never shows in my Immediate window. However, I created a test hyperlink that is text only and when selected, "Clicked!" appears. This indicates that Excel isn't treating the buttons as hyperlinks even though they have hyperlinks added to them.

The reason for the hyperlinks on the shape is for them to run code. I could use the assign macro feature to the shape, but in doing so I wouldn't be able to add a ScreenTip to the shape. I really want the ScreenTip as this will help future users know what the button is for.

Can someone please help me understand if this is possible?

Now, we link the button with the "hidden" cell, C5 in this example:

Now the hidden cell can only be selected by clicking the button. So if the Target in the Worksheet_SelectionChange event is the cell C5, we know that the button has been clicked.

To leave the previous selection unaffected, you can use the following code in the worksheet's code module:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static previousSelection As Range
    
    If Target.Address = "$C$5" Then
        Application.EnableEvents = False
        If Not previousSelection Is Nothing Then previousSelection.Select
        Application.EnableEvents = True
        
        Call ShapeClicked
    Else
        Set previousSelection = Target
    End If
End Sub

Sub ShapeClicked()
    MsgBox "The button has been clicked"
End Sub

CodePudding user response：

GWD's answer correctly solves the issue. I also found a slightly different way to work around this as well. Please see below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Not Intersect(Target, Range("B11")) Is Nothing Then
          Call dispatchLink(Target.Address)
     End If
End Sub