ControlTipText: Why can't I get at it or set it? Help.

[krazykaj]

Hi,

just a quick question:

Is there a way that i can add a ControlTipText feature (the little message that comes up when you hover your mouse over something.) to a commandButton that is NOT in a userform; one that is directly drawn on the excel sheet?

If I view the properties of a commandButton that has been created in a userform, there is a field to add ControlTipText. However, this field/option/setting is not there when i view the properties of a commandbutton that has been created directly on the excel sheet.

Why is this? Can i work around it?

If you have any help or suggestions, i would greatly appreciate it .

thankyou for you time,

Kind Regards,
KJ

 

[Tom Urtis]

Regarding your question about why this is the case, the reason is that activex controls benefit from the property of the object that hosts them. A worksheet is an object not designed for the inclusion of a ToolTip (aka "ControlTipText") property, whereas userform objects are. Complaints can be directed to Redmond, Washington, USA.

Regarding your question about a workaround, if your ole object is a command button for example, and its name is CommandButton1, then place the following code in the VBE as indicated. This is mostly from Bob Phillips with a few enhancements from me, so thanks to Bob for this:

In a standard 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 strCaption$

Public Function CreateToolTipLabel(objHostOLE As Object, strCaption As String) As Boolean
Application.ScreenUpdating = False

Dim objToolTipLbl As OLEObject, objOLE As OLEObject
Const SM_CXSCREEN = 0
Const COLOR_INFOTEXT = 23
Const COLOR_INFOBK = 24
Const COLOR_WINDOWFRAME = 6

For Each objOLE In ActiveSheet.OLEObjects
If objOLE.Name = "lblToolTip" Then objOLE.Delete
Next objOLE

Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")
strCaption = "This is my tooltip text."

With objToolTipLbl
.Top = objHostOLE.Top + objHostOLE.Height - 10
.Left = objHostOLE.Left + objHostOLE.Width - 10
.Object.Caption = strCaption
.Object.Font.Size = 10
.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 = "lblToolTip"
End With

DoEvents

Application.ScreenUpdating = True

Application.OnTime Now() + TimeValue("00:00:03"), "DeleteToolTipLabels"
End Function


Public Sub DeleteToolTipLabels()
Dim objToolTipLbl As OLEObject
For Each objToolTipLbl In ActiveSheet.OLEObjects
If objToolTipLbl.Name = "lblToolTip" Then objToolTipLbl.Delete
Next objToolTipLbl
End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


In the worksheet module:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim myToolTip As OLEObject, blnToolTip As Boolean

For Each myToolTip In ActiveSheet.OLEObjects
blnToolTip = myToolTip.Name = "lblToolTip"
Next myToolTip

If Not blnToolTip Then
CreateToolTipLabel CommandButton1, strCaption
End If
End Sub

 

[Ivan F Moala]

You could also try using the Toolbar control from the COMCTRL, this has a tooltip. If you run XP you can insert a Manifest file to get an XP look (As long as you use Win XP)
Have a look here

http://www.xcelfiles.com/ProgressBarXP.html

 

[krazykaj]

Wow . . .

Thankyou for the replies.

I didn't know that something I perceived so 'simple' would be so complex/involved :wink:

It would have taken me a while to figure all that out

Anyways, I’ll now go and play around with the code you both provided me with

Thankyou immensely again,

Cheers
KJ

 

[krazykaj]

hello again,

Tom Urtis, I hope it isn't too much trouble to ask but . . .

I have been trying to work backwards through the code you provided but I am having a bit of difficulty. (I am still relativly new to VB)
What exactly to i need to change so that i can have a tooltip for a second commandbutton?
The code you gave creates a tooltip for a button 'called' commandbutton1; What do i need to change/copy to get the code to work (and have a different tooltip message) for a second button called commandbutton2.

again, sorry if i am being a bit slow, but i just can't seem to get it to work and i don't know what i'm missing

again, thankyou for your time,

Kind Regards
KJ

 

[Tom Urtis]

If you really have two command buttons and one is named "CommandButton1" and the other is named "CommandButton2" then place the following code in your workbook's VBE as instructed below.

If you are saying you have 2 command buttons but really you have more than that, you should use a class module instead of a bunch of individual MouseMove events and a stacked Case structure in the Function.

Taking your question literally, considering that you have two embedded activex command buttons, delete the entire code from both modules that I posted before, so you don't get an ambiguous compile error.

Then...

This in a standard 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 strCaption$
Public WhichButton$

Public Function CreateToolTipLabel(objHostOLE As Object, strCaption As String) As Boolean
Application.ScreenUpdating = False

Dim objToolTipLbl As OLEObject, objOLE As OLEObject
Const SM_CXSCREEN = 0
Const COLOR_INFOTEXT = 23
Const COLOR_INFOBK = 24
Const COLOR_WINDOWFRAME = 6

For Each objOLE In ActiveSheet.OLEObjects
If objOLE.Name = "lblToolTip" Then objOLE.Delete
Next objOLE

Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")

Select Case WhichButton
Case "CommandButton1"
strCaption = "This is my tooltip text for CommandButton1."
Case "CommandButton2"
strCaption = "This is my tooltip text for CommandButton2."
Case ""
strCaption = "Tooltip text looking for a home."
End Select

With objToolTipLbl
.Top = objHostOLE.Top + objHostOLE.Height - 10
.Left = objHostOLE.Left + objHostOLE.Width - 10
.Object.Caption = strCaption
.Object.Font.Size = 10
.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 = "lblToolTip"
End With

DoEvents

Application.ScreenUpdating = True

Application.OnTime Now() + TimeValue("00:00:03"), "DeleteToolTipLabels"
End Function


Public Sub DeleteToolTipLabels()
Dim objToolTipLbl As OLEObject
For Each objToolTipLbl In ActiveSheet.OLEObjects
If objToolTipLbl.Name = "lblToolTip" Then objToolTipLbl.Delete
Next objToolTipLbl
End Sub



This in the worksheet module:

Option Explicit

Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
WhichButton = "CommandButton1"
Dim myToolTip As OLEObject, blnToolTip As Boolean

For Each myToolTip In ActiveSheet.OLEObjects
blnToolTip = myToolTip.Name = "lblToolTip"
Next myToolTip

If Not blnToolTip Then
CreateToolTipLabel CommandButton1, strCaption
End If
End Sub

Private Sub CommandButton2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
WhichButton = "CommandButton2"
Dim myToolTip As OLEObject, blnToolTip As Boolean

For Each myToolTip In ActiveSheet.OLEObjects
blnToolTip = myToolTip.Name = "lblToolTip"
Next myToolTip

If Not blnToolTip Then
CreateToolTipLabel CommandButton2, strCaption
End If
End Sub

 

[krazykaj]

Thankyou,

Yes i have two buttons on my worksheet that i'd lke to add tooltips to.

Thankyou for your excellent help again,

Cheers
KJ