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.
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.
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)
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