Tooltip on a form button

Question

Hi,

I have a button in my spreadsheet that runs a macro.

the button is from the Forms category (not ActiveX).

can I display a tooltip when the mouse is placed over the button?

Thanks,

Yuval

Answer 1

No, the Button (form control) did not support tooltips.

The CommandButton (ActiveX control) did support tooltips (ControlTipText property), but only when placed on a UserForm.

In a sheet we can use a trick to get something similar as a tooltip on ActiveX controls:

When we add a comment in one of the cells behind the control, we can use some code in the mouse events and make it visible.

The code below is the very simple way. If you have many controls, it is more comfortable to use a class module to capture all controls in a file automatically.

Here you can download an example.

https://dl.dropboxusercontent.com/u/35239054/ControlTooltip.xls

Andreas.

Private Sub ShowComment(ByVal Control As Object, _

    ByVal Button As Integer, ByVal Shift As Integer, _

    ByVal X As Single, ByVal Y As Single)

  'Show (or hide) the comment of an underling cell of an ActiveX control (if any)

  Const EdgeLimit = 5 'Points

  Const WaitAfterClick = 1 'Second

  Dim R As Range, C As Comment

  Static LastCall As Single

  With Control

    'Find a comment in the surrounding cells

    For Each R In Range(.TopLeftCell, .BottomRightCell)

      Set C = R.Comment

      If Not C Is Nothing Then Exit For

    Next

    'Found?

    If C Is Nothing Then Exit Sub

    'Show or hide it

    C.Visible = (X > EdgeLimit) And (X < .Width - EdgeLimit) And _

      (Y > EdgeLimit) And (Y < .Height - EdgeLimit) And _

      (Button = 0) And (Shift = 0) And (Timer - LastCall > WaitAfterClick)

    'Setup the timer if the control gets a click

    If (Button <> 0) Then LastCall = Timer

  End With

End Sub

Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _

    ByVal X As Single, ByVal Y As Single)

  ShowComment CommandButton1, Button, Shift, X, Y

End Sub

Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _

    ByVal X As Single, ByVal Y As Single)

  ShowComment CommandButton1, Button, Shift, X, Y

End Sub

Answer 2

Andreas,

Thanks for your response.

I have found in the web a nice workaround that works pretty well.

here is a link to it. look for:

**group controls and add a hyperlink with ScreenTip**

Regards,

Yuval