Solved: tooltip

[crush]

On a worksheet I have an image to which I have assigned a macro. When the mouse pointer hovers over the image, a 'link hand' appears, indicating you can click the button to start the macro. Now, when the mouse pointer hovers over the image, is there a way to display a 'tooltip' to say what clicking the image will do?

[mikerickson]

The hovering hand indicates that you can "grab" the shape (image) and drag it around the sheet.

Since it isn't a control, I don't think (a.k.a. I can't figure out how to) one can set the .ControlTipText, as one could for a Command Button or other control. (Shapes controls (from the Forms menu) are different from the ActiveX controls (from the Toolbox). I don't think that you can set the .ControlTipText of a shapes control.

[crush]

thanks for taking the time to reply. when i add an activeX control to a worksheet, such as a commandbutton, or image control and check the properties, the 'controltip text' property is not available. If i add the same command button or image control to a userform, i see the controltip text property. i guess tool tips are only meant for use on user forms and not worksheets, which is a petty!

i was hoping someone else figured a way to do this for controls or shapes on a worksheet.

[mikerickson]

There might be some way to massage a Comment to show what you want.

[Bob Phillips]

To do this, put the following code in a standard code module (making it
available to the whole workbook).

[vba]

Option Explicit

Declare Function GetSystemMetrics Lib "user32" ( _
ByVal nIndex As Long) As Long

Declare Function GetSysColor Lib "user32" ( _
ByVal nIndex As Long) As Long


Public Function CreateToolTipLabel(objHostOLE As Object, _
sTTLText As String) As Boolean
Dim objToolTipLbl As OLEObject
Dim objOLE As OLEObject

Const SM_CXSCREEN = 0
Const COLOR_INFOTEXT = 23
Const COLOR_INFOBK = 24
Const COLOR_WINDOWFRAME = 6

Application.ScreenUpdating = False 'just while label is created and formatted

For Each objOLE In ActiveSheet.OLEObjects
If objOLE.Name = "TTL" Then objOLE.Delete 'only one can exist at a time
Next objOLE

'create a label control...
Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")

'...and format it to look as a ToolTipWindow
With objToolTipLbl
.Top = objHostOLE.Top + objHostOLE.Height - 10
.Left = objHostOLE.Left + objHostOLE.Width - 10
.Object.Caption = sTTLText
.Object.Font.Size = 8
.Object.BackColor = GetSysColor(COLOR_INFOBK)
.Object.BackStyle = 1
.Object.BorderColor = GetSysColor(COLOR_WINDOWFRAME)
.Object.BorderStyle = 1
.Object.ForeColor = GetSysColor(COLOR_INFOTEXT)
.Object.TextAlign = 1
.Object.AutoSize = False
.Width = GetSystemMetrics(SM_CXSCREEN)
.Object.AutoSize = True
.Width = .Width + 2
.Height = .Height + 2
.Name = "TTL"
End With
DoEvents
Application.ScreenUpdating = True

'delete the tooltip window after 5 secs
Application.OnTime Now() + TimeValue("00:00:05"), "DeleteToolTipLabels"

End Function

Public Sub DeleteToolTipLabels()
Dim objToolTipLbl As OLEObject
For Each objToolTipLbl In ActiveSheet.OLEObjects
If objToolTipLbl.Name = "TTL" Then objToolTipLbl.Delete
Next objToolTipLbl
End Sub
[/vba]

Then in the code module for the sheet that has the control, add some
mousedown event code. To get to this module, right-click on the sheet name
tab, and selecw code (or double-click on the sheet name from within the VB
IDE). Here is an example of how to call it, assuming that the command button
is called Image

[vba]

Private Sub Image1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
Dim objTTL As OLEObject
Dim fTTL As Boolean

For Each objTTL In ActiveSheet.OLEObjects
fTTL = objTTL.Name = "TTL"
Next objTTL

If Not fTTL Then
CreateToolTipLabel cmdTooltipTest, "ToolTip Label"
End If

End Sub
[/vba]

[crush]

Hello XLD

Sorry for not replying sooner, WOW! This is way cool!! I copied what you posted, and had to make one minor change.

From this:

IfNot fTTL Then CreateToolTipLabel cmdTooltipTest, "ToolTip Label" EndIf

To this:

IfNot fTTL Then CreateToolTipLabel Image1, "ToolTip Label" EndIf

And as the mouse cursor passed moved over the command button (called Image1), hey presto, I had a Tool Tip named 'Tool Tip Label'

The only other changes I made were to the font size and tool tip size cus my eyes are not as good as they used to be

thank you very much, this is a useful piece of code to polish up a UI.

Once again, this forum never ceases to surprise me.

[Bob Phillips]

It is a piece of code that I use for command buttons. I changed it for an image, but obviously missed one reference.

Glad that you liked it. It is nice that the tooltip is highly configurable isn't it?

[crush]

Hello again,

i do like this piece of code. i did run into a couple of quirks. I hae several images to which I applied a tooltip, then as the tooltip is displayed i continue to click the image to run a macro. it did not always seem to like to run the macro while the tip was hovering, have you noticed any errors like this before?

[Bob Phillips]

Can't say I have noticed that myself.

I assume you are running the code in the Image Click event?

[lolster]

Not to necro an old thread, but I tried using the code provided and made the Image1 change and put the code in a new module and the worksheet code, fiddled around with a command button called Image/Image1, saved in both instances, reopened the Excel doc, but have yet to have any ToolTip display when mousing over. I'm using Excel 2007. Why is this happening and how can I fix it?