Floating Comments over Command Button

[suriyahi]

Hello, I have few of the Command Buttons (Forms toolbar based) and when a user does 'Mouse Over' any of those Command Buttons, I would want Excel to show little definition about those Command Buttons. How is this possible? I appreciate your help and Thanks in advance!

[RonMcK]

suriyahi,

In the Properties panel of VBE, look for 'ControlTipText' , type the text you want your users to see in the space to the right of the property name.

Cheers,

[dominicb]

Good afternoon suriyahi

when a user does 'Mouse Over' any of those Command Buttons, I would want Excel to show little definition about those Command Buttons.

Andy Pope has suggested a neat little solution in this thread that works well.

HTH

DominicB

[GTO]

...I have few of the Command Buttons (Forms toolbar based) and ...

Hello Suriyahi,

With kind regards for both Ron and DominicB, I believe your specificity as to forms-based command buttons may have been overlooked.

Might I suggest substituting ActiveX type command buttons? These would give you the available properties you are looking for.

Mark

[suriyahi]

I tried the link posted above and this is something exactly what I need. But, sad thing is I am not following at all how this works. I guess, I need to be walked step by step. Please see link below that Andy Pope posted:

http://www.excelforum.com/excel-prog...-a-button.html

See Post #11. I need to understand that code. If any one can help me with this I would really appreciate it. Thanks in advance!!

[suriyahi]

Can any one please tell me what and how to follow example given by Andy Pope so that I can apply this to my book? I appreciate it. Thanks in advance!

[suriyahi]

Any one...Please? Thanks!

[GTO]

Hey Suri,

I'm not a member of the other forum, so can't "see" the attachment. That said, I wanted to correct myself, as I had advised that you this could be done with a button from the control toolbox. Uhmm, my bad info as this is not true.

Sorry about that,

Mark

[Bob Phillips]

No it is not untrue

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 cmdTooltipTest

[vba]
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
[/vba]

[Andy Pope]

Replied to your cross post at MrExcel
http://www.mrexcel.com/forum/showthread.php?t=348009

What was wrong with posting a question on the original site?

[GTO]

No it is not untrue...

@xld:

Good morning, or actually afternoon for you. Sorry for lacking clarity as I was referring to 'normal' properties; nifty workaround!

[suriyahi]

HI XLD,
I am trying your code and come across some problem. You said the following:
"assuming that the command button
is called cmdTooltipTest"
And, what if the command buttons I have are Forms based and is named something as 'Data Management System'. With that in mind, how can I tweak the coding? I appreciate you looking into it. Also, do you have a sample spreadsheet for code you have provided? Thanks a tons!!

[Bob Phillips]

If they are forms buttons on a worksheet it doesn't work.

[suriyahi]

Oh no...any other solutions?

[GTO]

Sir:

NO, there is no other solution to a mouseover or tooltip text for a FORMS BASED bttn. It doesn't exist.

This was explained at (http://www.excelforum.com/excel-prog...-a-button.html) #4 of where you originally stumbled onto the workaround reference a control from the control toolbox. To wit: "There isn't a property to do this with those controls"

At (http://www.mrexcel.com/forum/showthread.php?t=348009) #10, Mr. Pope again explains this, to wit: "In reply to a PM from sjha. They are all controls from the Control Toolbox. Form controls do not support any events other than Click. And in fact this is not an event just the execution of the OnAction property. With form buttons you can not re create this."

At #10 (here), Andy politely redirects you to his answer regardless of your cross-posting.

At #13, XLD answers this yet again.

I would respectfully mention that repeated asking of the same question, wherein said question has been clearly answered, is in my opinion, impolite and abusive/wasteful of the time and energy of those who you are asking.

Mark