Adding tool tip to worksheet button

[Guest]

I have a button in a worksheet cell that runs a VBA sub (fortunately this
works fine!)

Is there any way to add a tool tip to the button?

Thanks
Sue

 

[Frank Kabel]

Hi
if it's a button from the toolbox toolbar set the ControlTip property
of this button

 

[Dave Peterson]

Setting the ControlTipText works ok in a userform. But I couldn't get it to
work on a commandbutton on a worksheet.

 

[Guest]

It's a control button added from the Control Toolbox toolbar, but if I
right-click for the properties listing it doesn't show an entry for
ControlTipText.

I'm puzzled!

Sue

 

[Bob Phillips]

That is true, it is only on forms, userforms, that has that property.

It can be emulated with control toolbox buttons using a few frigs. Here is
an example for a button named CmdTooltipTest

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

'------------- bas module ------------------------
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 3 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


'------------end of bas module -------------

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 select 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 cmdTooltipTest

Private Sub CmdTooltipTest_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


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob -

Wow! Many thanks for this - I am only just starting VBA programming so your
suggestion may be a little too much for me right now, but I shall come back
to it later.

Sue

 

[Bob Phillips]

Sue,

It's pretty straight-forward if you follow my guiding notes. Just change the
text to what you require.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Bob -

Wow! Many thanks for this - I am only just starting VBA programming so your
suggestion may be a little too much for me right now, but I shall come back
to it later.

Sue