excelvbaeventshyperlinkworksheet

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


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

I want to use the FollowHyperlink worksheet event to recognize the shape that is clicked.

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

When clicking on the shapes, they point to A1 and "Clicked!" never shows in my Immediate window.
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 want the ScreenTip as this will help future users know what the button is for.

Screenshot of buttons
enter image description here


Solution

  • There is a workaround for this problem. Instead of the Worksheet_FollowHyperlink event, you can use the Worksheet_SelectionChange event.

    To do this, you need a cell that is completely covered up by your button. If the button is too small to cover up a cell, you can just hide a row and a column and place the button at the intersection of the hidden row and column.

    Button at the intersection of column "C" and row 5

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

    Linking the button with the cell C5

    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)
        Const HIDDEN_CELL_ADDESS As String = "$C$5" '<--Set hidden cell address here
        Static previousSelection As Range
    
        If Target.Address = HIDDEN_CELL_ADDESS Then
            'Make sure the linked cell doesn't stay selected, otherwise the next 
            'click on the button may not be recognized
            Application.EnableEvents = False
            If Not previousSelection Is Nothing Then previousSelection.Select
            If TypeName(Selection) = "Range" Then
                If Selection.Address = HIDDEN_CELL_ADDESS Then Target.Offset(1).Select
            End If
            Application.EnableEvents = True
    
            Call ShapeClicked
        Else
            Set previousSelection = Target
        End If
    End Sub
    
    Sub ShapeClicked()
        MsgBox "The button has been clicked"
    End Sub