Tooltip and Macro on a shape in Excel, VBA

[xlsat]

I am trying to attach a tooltip (through hyperlink route) and a macro to an existing shape. The code is something like this -

Sub testtooltip()
    Dim myDocument As Worksheet
    Dim shp As Shape
    Dim strTooltip As String, strMacroName As String
    
    Set myDocument = Sheets("MyDashboard")
    
    strTooltip = "setting this tooltip - "
    strMacroName = "'" & ActiveWorkbook.Name & "'" & "!" & "RefreshDashboard"
    
    With myDocument
        Set shp = .Shapes("shp_button_refresh")
        
        .Hyperlinks.Add Anchor:=shp, Address:="", ScreenTip:=strTooltip
       shp.OnAction = strMacroName
    End With

End Sub

I am calling this Sub on Workbook_Open. As I see, the tooltip gets assigned to the shape without any problem and also, the Macro name too seems to get assigned. BUT, on click of the shape, nothing happens, meaning, the assigned macro never gets called. If I comment out the tooltip assigning line, then macro gets called!!!

I did see similar macro not getting called problem being posted in a few places, but none of them seemed to provide a proper answer Help please?

Note: My Excel version is 2007.
P.S: I had submitted this as a response to another old thread. Posting it in a new thread realizing that the old one may not surface at all...

 

[sijpie]

I can imagine that a shape (or soemthing else for that matter) cannot be a hyperlink and a macrobutton at the same time. What is excel to do when you click the shape - run the macro or follow the hyperlink? That is ambiguous, and so only one action is possible.

 

[xlsat]

Ah! The point is that the hyperlink given through the code would be a dummy one pointing to nothing, hence Macro should run. Help of hyperlink is taken since we want to show the tooltip.

For many, it seems to be working since I got this solution from one of these threads itself. The issue seems to be that, in some cases it wont work - the reason for that is what is eluding me at this point.

 

[AutoFilterQueen]

I found a solution to this thread that may work, especially if the shape is rectangular. There are two different ways. One involves using a CommandButton, and the other involves placing the picture inside a chart object. The chart object would work for a non-rectangular shape. I preferred the behavior of the chart object option. I wanted to post this here in case someone runs across this thread looking for this solution, as I did. ScreenTip for an Image (Microsoft Excel)

 

[geoffm]

It seems like when both a hyperlink and a macro are assigned to a shape, both are run when it is clicked.
It is possible through VBA to create a hyperlink with no destination address.
I created this simple macro for this purpose, and I created a button in my quick-access-toolbar to run it on selecte shapes, it works just great. The looping allows to assign a tooltip to many shapes at once.

Public Sub AssignTooltipToImage()
   ' On Error Resume Next
    Dim text As String
    text = InputBox("Tooltip text")
    For Each s In Selection.ShapeRange
        ActiveSheet.Hyperlinks.Add s, "", "", ScreenTip:=text
    Next
End Sub

 

[Jaafar Tribak]

You can use this:

Example:
Add 3 shapes to the active worksheet and assing to each shape a Macro as usual.

Now place the following code in the ThisWorkbook module and run the Workbook_Open event (or save, close and reopen the workbook) in order for the code to take effect.

Code in the ThisWorkbook Module:

Option Explicit

Private WithEvents cmb As CommandBars

Private Type POINTAPI
    x As Long
    y As Long
End Type

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private Sub Workbook_Open()
    Call AddToolTipToShape(Shp:=ActiveSheet.Shapes(1), ScreenTip:="This is tooltip for shape 1")
    Call AddToolTipToShape(Shp:=ActiveSheet.Shapes(2), ScreenTip:="Hello from shape 2")
    Call AddToolTipToShape(Shp:=ActiveSheet.Shapes(3), ScreenTip:="bla bla bla ...")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call CleanUp
End Sub

Private Sub AddToolTipToShape(ByVal Shp As Shape, ByVal ScreenTip As String)
    On Error Resume Next
    Shp.Parent.Hyperlinks.Add Shp, "", "", ScreenTip:=ScreenTip
    Shp.AlternativeText = Shp.AlternativeText & "-ScreenTip"
    Set cmb = Application.CommandBars
End Sub

Sub CleanUp()
    Dim ws As Worksheet, Shp As Shape
    On Error Resume Next
    For Each ws In Me.Worksheets
        For Each Shp In ws.Shapes
            If InStr(1, Shp.AlternativeText, "-ScreenTip") Then
                Shp.Hyperlink.Delete
                Shp.AlternativeText = Replace(Shp.AlternativeText, "-ScreenTip", "")
            End If
        Next Shp
    Next ws
End Sub

Private Sub cmb_OnUpdate()
    Dim tPt As POINTAPI
    GetCursorPos tPt
    If InStr(1, "RangeNothing", TypeName(ActiveWindow.RangeFromPoint(tPt.x, tPt.y))) = 0 Then
        If ActiveWindow.RangeFromPoint(tPt.x, tPt.y).OnAction <> "" Then
            If GetAsyncKeyState(vbKeyLButton) Then
                Application.Run (ActiveWindow.RangeFromPoint(tPt.x, tPt.y).OnAction)
            End If
        End If
    End If
End Sub

Now, each shape should have a screen tooltip assigned to it while still running its associated macro when clicking on it.

This workaround should also work for shapes located in different worksheets .. Just pass the correct target shape (name or index) to the AddToolTipToShape sub.

 

[geoffm]

Thanks, this actually works. The way I understand it it adds an hyperlink to the shape and creates a "commandbar" event handler which executes when a shape is clicked to workaround the fact the hyperlink "steals" the click from the macro.

However there is an important problem with it, since cmd works like a global variable, when there is a macro Error the event handler is lost and so the hyperlinks prevent the macros from running. I haven't found a workaround for this.

 

[Jaafar Tribak]

Thanks, this actually works. The way I understand it it adds an hyperlink to the shape and creates a "commandbar" event handler which executes when a shape is clicked to workaround the fact the hyperlink "steals" the click from the macro.

However there is an important problem with it, since cmd works like a global variable, when there is a macro Error the event handler is lost and so the hyperlinks prevent the macros from running. I haven't found a workaround for this.

Short of running a continious loop or a timer which are both problemactic performance-wise, you can re-hook the commandbars as soon as a cell is selected... Not perfect but should work.


Try adding this at the bottom of the code :

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set cmb = Application.CommandBars
End Sub

 

[geoffm]

Thanks for the suggestion, however I never, ever use a selection change event as it removes all possibility of using the Undo functionnalities.
I'm actually making a switch from using icons to using menus (with comboboxes) to launch macros. I feel it is easier to implement and maintain and it clutters the interface much less, so my need for tooltips is less crucial.
I might still use your tooltips though. I have a similar problem with some global variables that I use, so I ended up putting all in a "SetGlobalVariables" sub that is run at the beginning of any sub that uses them.

 

[Jaafar Tribak]

Thanks for the suggestion, however I never, ever use a selection change event as it removes all possibility of using the Undo functionnalities.
I'm actually making a switch from using icons to using menus (with comboboxes) to launch macros. I feel it is easier to implement and maintain and it clutters the interface much less, so my need for tooltips is less crucial.
I might still use your tooltips though. I have a similar problem with some global variables that I use, so I ended up putting all in a "SetGlobalVariables" sub that is run at the beginning of any sub that uses them.

Using the selection change event in this particular scenario will not remove the Undo functionnality because the line Set cmb = Application.CommandBars dosesn't affect the workbook/worksheets interface ... Try it and you will see.