Solved: How to create a tooltip text / comments with VBA

[hometech]

Hello Guys, i have a command button placed in my word document. i want it to be able to display a tooltip text when the mouse hovers over it.

The issue now is that the VBA/form object property does not provide a tooltip property for the command button.

I also tried using the hyperlink feature in MS Word to produce a tooltip text/comment on the command butt when hovered, it works only when the design mode button on the developer tab is active (when this button is active, it does not execute commands tied to the click events but it displays the comments/tooltip text) but when the developer button is not active, the tooltip text does not show but the command button works fine.

So is there any way to go about achieving the tooltip text on the command button via VBA or if there is any other way to work with the hyperlink feature without it show the extra click to follow link it shows below the tooltip message.

[skipres]

This doesn't answer your question directly, but I think that you might be able to use it as a starting point.

I open a form called actionFunction when any word document is openend (code is in my normal.docm) It has a reminder message and at the end, tells the user to just mouse over the message and it will go away.

Here is the code
Private Sub actionFunction_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Me.Hide
Unload ActionForm
End Sub

I would think that you could use the MouseMove and/or MouseOver function to create a psuedo tool tip...

[fumei]

There is no MouseOver, but MouseMove acts like a mouse over.
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
MsgBox "This button does yadda"
End Sub

Except......every time you move the mouse over the commandbutton, you get the MouseMove event, and you can sometimes not actually click the button.

[hometech]

Okay thanks guys, i'll try this out and give you my feedback soon.
Thanks.

[hometech]

Men this is really so not it at all.... the msgBox option is so not it, anyway guys will still keep working for other ways to walk around it.

Thanks guys...

[Frosty]

Instead of a messagebox, you could try displaying a modeless userform with the info you want. I'm not sure how you'd dismiss the form... Either some kind of timer functionality, but you'd get more of a floating thing with that (as well as the ability to position the form)

[hometech]

Yes frosty i agree with you, but then there will still be no way to have a click event for the same command button.

Thanks

[Frosty]

Well, this intrigued me, so I gave it a go at giving some kind of proof-of-concept as a brain teaser. This is by no means any sort of polished product, and I may be unable to take it much further, but this uses the WinAPI call to find out your cursor position, and then uses a combination of a modeless form and the Application.OnTime functionality.

Everything is default- so it's called CommandButton1, UserForm1, Module1, etc. There is no error trapping. But if you can try to incorporate from here, please let us know what you discover.

In the ThisDocument module:

Option Explicit
Private Sub CommandButton1_Click()
Unload UserForm1
MsgBox "run me"
End Sub
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal x As Single, _
ByVal y As Single)
Dim lCurX As Long
Dim lCurY As Long

GetCursorPosition lCurX, lCurY
ShowMe lCurX, lCurY
End Sub

In the UserForm1 module
NOTE: this userform needs to have ShowModal set to FALSE

Option Explicit
Private Sub UserForm_Activate()
'start a timer of 5 seconds for the form to be dismissed, if no input
Application.OnTime When:=Now + TimeValue("00:00:01"), Name:="DismissMe"
End Sub
Private Sub UserForm_Initialize()
bShowing = True
End Sub
Private Sub UserForm_Terminate()
bShowing = False
End Sub

In the Module1 module:

Option Explicit
Public bShowing As Boolean

'Info about this at: http://support.microsoft.com/kb/152969
' Access the GetCursorPos function in user32.dll
Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

' GetCursorPos requires a variable declared as a custom data type
' that will hold two integers, one for x value and one for y value
Type POINTAPI
X_Pos As Long
Y_Pos As Long
End Type

Sub GetCursorPosition(x As Long, y As Long)
Dim Hold As POINTAPI

'use the API to return our public type
GetCursorPos Hold

'return them back outside the sub as the passed in parameters
x = Hold.X_Pos
y = Hold.Y_Pos

End Sub

Public Sub ShowMe(lWhereX As Long, lWhereY As Long)
If bShowing = False Then
bShowing = True
UserForm1.Show
End If
UserForm1.Top = lWhereY
UserForm1.Left = lWhereX
End Sub
Public Sub DismissMe()
Unload UserForm1
bShowing = False
End Sub

This is almost embarrassingly unpolished code, but I used this as more of an escape from my real work, so apologies for not giving more explanation. Perhaps this can give someone else an idea of how to give you a more polished end-product.

[hometech]

Hey frosty, this is awesome, already having a good feeling about this by merely looking at it.

Will try this out and get back to you soon.

Much thanks.

[gmaxey]

hometech,
Interesting yes, but I couldn't get it to work. I've tried to incorportate Jason's idea in some older code I've used before and while it "almost" works, if the user lets the mouse come to a stop in the command button then the tip will flicker every one second. I would also like to position the tip at fixed point and it not continue to move about, but I'm stuck there as well.

Option Explicit
Private Sub CommandButton1_Click()
Module1.DismissMe
MsgBox "run me"
End Sub
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal x As Single, ByVal y As Single)
dblWidth = Me.CommandButton1.Width
dblWidth = Me.CommandButton1.Height
Module1.ShowMe
End Sub
Module1
Option Explicit
Public bShowing As Boolean
Public dblWidth As Double
Public dblHeight As Double
Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Declare Function GetDeviceCaps Lib "Gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDC As Long) As Long
Const LOGPIXELSX = 88
Const LOGPIXELSY = 90
Private Declare Function GetCursorPos Lib "user32" (lngPosit As typXYPosit) As Long
Public Type typXYPosit
Left As Long
Top As Long
End Type
Dim m_XRes As Long
Dim m_YRes As Long
Public Function PointPerPixel_X() As Double
Dim hDC As Long
hDC = GetDC(0)
m_XRes = GetDeviceCaps(hDC, LOGPIXELSX)
PointPerPixel_X = 72 / GetDeviceCaps(hDC, LOGPIXELSX)
ReleaseDC 0, hDC
End Function
Public Function PointPerPixel_Y() As Double
Dim hDC As Long
hDC = GetDC(0)
m_YRes = GetDeviceCaps(hDC, LOGPIXELSY)
PointPerPixel_Y = 72 / GetDeviceCaps(hDC, LOGPIXELSY)
ReleaseDC 0, hDC
End Function
Public Function MousePosit() As typXYPosit
Dim typMousePosit As typXYPosit
GetCursorPos typMousePosit
MousePosit = typMousePosit
End Function
Public Function ConvertMousePositToFormPosit() As typXYPosit
Dim typFormPosit As typXYPosit
typFormPosit = MousePosit
typFormPosit.Left = PointPerPixel_X * typFormPosit.Left
typFormPosit.Top = PointPerPixel_Y * typFormPosit.Top
ConvertMousePositToFormPosit = typFormPosit
End Function
Public Sub ShowMe()
If bShowing = False Then
bShowing = True
UserForm1.Show vbModeless
End If
UserForm1.Left = ConvertMousePositToFormPosit.Left + PointsToPixels(CSng(dblWidth)) / m_XRes * PointsToPixels(CSng(dblWidth))
UserForm1.Top = ConvertMousePositToFormPosit.Top + PointsToPixels(CSng(dblHeight)) / m_YRes * PointsToPixels(CSng(dblHeight))
Application.OnTime When:=Now + TimeValue("00:00:01"), Name:="DismissMe"
End Sub
Public Sub DismissMe()
Unload UserForm1
bShowing = False
End Sub

UserForm1
Option Explicit
Private Sub UserForm_Initialize()
bShowing = True
End Sub
Private Sub UserForm_Terminate()
bShowing = False
End Sub

[gmaxey]

Jason,

I'm a bit bleary eyed from looking at this stuff, but leveraging off of your idea, I think I have found a pretty interesting way of doing this.

Iv'e still not been able to keep the tip perfectly stationary, but it is close.


ThisDocument
Option Explicit
Private Sub CommandButton1_Click()
Module1.DismissMe
MsgBox "run me"
End Sub
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
dblWidth = Me.CommandButton1.Width
dblHeight = Me.CommandButton1.Height
Module1.ShowMe X, Y
End Sub


UserForm1
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'This ensures the tip is closed if the user rapidly shifts the mouse from the control to the tip.
Unload Me
End Sub


Module1
Option Explicit
Public dblWidth As Double
Public dblHeight As Double
Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Declare Function GetDeviceCaps Lib "Gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDC As Long) As Long
Const LOGPIXELSX = 88
Const LOGPIXELSY = 90
Private Declare Function GetCursorPos Lib "user32" (lngPosit As typXYPosit) As Long
Public Type typXYPosit
Left As Long
Top As Long
End Type
Dim m_XRes As Double
Dim m_YRes As Double
Public Function PointPerPixel_X() As Double
Dim hDC As Long
hDC = GetDC(0)
PointPerPixel_X = 72 / GetDeviceCaps(hDC, LOGPIXELSX)
m_XRes = PointPerPixel_X
ReleaseDC 0, hDC
End Function
Public Function PointPerPixel_Y() As Double
Dim hDC As Long
hDC = GetDC(0)
PointPerPixel_Y = 72 / GetDeviceCaps(hDC, LOGPIXELSY)
m_YRes = PointPerPixel_Y
ReleaseDC 0, hDC
End Function
Public Function MousePosit() As typXYPosit
Dim typMousePosit As typXYPosit
GetCursorPos typMousePosit
MousePosit = typMousePosit
End Function
Public Function ConvertMousePositToFormPosit() As typXYPosit
Dim typFormPosit As typXYPosit
typFormPosit = MousePosit
typFormPosit.Left = PointPerPixel_X * typFormPosit.Left
typFormPosit.Top = PointPerPixel_Y * typFormPosit.Top
ConvertMousePositToFormPosit = typFormPosit
End Function
Public Sub ShowMe(X, Y)
'Use a band of space (like crossing a border) to trigger the form on and off.
'Eliminates timer and flickering.
If X > 3 And X < (dblWidth - 3) Then
If Y > 3 And Y < (dblHeight - 3) Then
UserForm1.Left = ConvertMousePositToFormPosit.Left + ((dblWidth + 10) / m_XRes) - X / m_XRes
UserForm1.Top = ConvertMousePositToFormPosit.Top - Y / m_YRes
UserForm1.Show vbModeless
Else
Module1.DismissMe
End If
Else
Module1.DismissMe
End If
End Sub
Public Sub DismissMe()
Unload UserForm1
End Sub

[gmaxey]

Jason,

It intriqued me also. By incorporating a modified version of Chip Pearson's FormControl module, I've been able to show the form without the title bar and with opacity.

On my PC (I always use Print View and zoom set to page width), I can display a pseudo tool tip just above or just to the left of the command button.

I've also managed to get it relatively stable, it still "wiggles" a little if the user wiggles the mouse while the in the control but it no longer jumps around.

The process is not entirely my own work. In addition to yours, I am using some code that I plucked off Google. So I am not sure why the LOGPIXELSX and LOGPIXELSY constants are what they are :dunno.

With the modified FormModule, the code is getting pretty long, so I am posting a document.

You and a lot of the other regulars here, are a heck of a lot smarter than me, so if it can be improved, I figure I'll pass it back and let you have a go at it.

Thanks, Greg


Well, this intrigued me, so I gave it a go at giving some kind of proof-of-concept as a brain teaser. This is by no means any sort of polished product, and I may be unable to take it much further, but this uses the WinAPI call to find out your cursor position, and then uses a combination of a modeless form and the Application.OnTime functionality.

Everything is default- so it's called CommandButton1, UserForm1, Module1, etc. There is no error trapping. But if you can try to incorporate from here, please let us know what you discover.

In the ThisDocument module:

Option Explicit
Private Sub CommandButton1_Click()
Unload UserForm1
MsgBox "run me"
End Sub
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal x As Single, _
ByVal y As Single)
Dim lCurX As Long
Dim lCurY As Long

GetCursorPosition lCurX, lCurY
ShowMe lCurX, lCurY
End Sub

In the UserForm1 module
NOTE: this userform needs to have ShowModal set to FALSE

Option Explicit
Private Sub UserForm_Activate()
'start a timer of 5 seconds for the form to be dismissed, if no input
Application.OnTime When:=Now + TimeValue("00:00:01"), Name:="DismissMe"
End Sub
Private Sub UserForm_Initialize()
bShowing = True
End Sub
Private Sub UserForm_Terminate()
bShowing = False
End Sub

In the Module1 module:

Option Explicit
Public bShowing As Boolean

'Info about this at: http://support.microsoft.com/kb/152969
' Access the GetCursorPos function in user32.dll
Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

' GetCursorPos requires a variable declared as a custom data type
' that will hold two integers, one for x value and one for y value
Type POINTAPI
X_Pos As Long
Y_Pos As Long
End Type

Sub GetCursorPosition(x As Long, y As Long)
Dim Hold As POINTAPI

'use the API to return our public type
GetCursorPos Hold

'return them back outside the sub as the passed in parameters
x = Hold.X_Pos
y = Hold.Y_Pos

End Sub

Public Sub ShowMe(lWhereX As Long, lWhereY As Long)
If bShowing = False Then
bShowing = True
UserForm1.Show
End If
UserForm1.Top = lWhereY
UserForm1.Left = lWhereX
End Sub
Public Sub DismissMe()
Unload UserForm1
bShowing = False
End Sub

This is almost embarrassingly unpolished code, but I used this as more of an escape from my real work, so apologies for not giving more explanation. Perhaps this can give someone else an idea of how to give you a more polished end-product.

[Frosty]

You're right, that's a lot of code. But a much better approach to define the band of space. I knew there had to be a better approach.

The hiding of the title bar and the opacity are nice additions, but the key (to me) seems to be the defining of the area by which to show/load the form and use that same area to hide the form.

That said... I think there are still some problems... one of which is that this process seems to disable other interactions with the document, and I wonder what happens if you use the mouse wheel to scroll the document (I don't have a mouse wheel to test).

But I did notice a little bit of strangeness in trying to interact with the document while the tooltip was hovering... so to be really complete, it may be necessary to capture some other events as well (window/document switching, selection moving, etc). Like anything, to make it really production ready is probably more trouble than it's worth. Especially considering that the OP could simply use...

Application.StatusBar = "Do your info here" kind of process for informing the end-user of some info. It's not a tooltip, but it is a way of getting info to the user.

Thanks for taking it a bit further... I think that might be as far as a proof of concept can go without knowing real design requirements. And I learned something (again) from you-- thanks!

[fumei]

Most interesting. I have some odd behaviour.

Placing the mouse cursor at the very very top of the commandbutton gives the userform display, but small - let's call it a font size of 8 (I am making that up).

If I move the point a tiny bit south, the display size increases to - let's call it normal. It is the display size for 95% of the area of the commandbutton. Only pointing at the very top gives the small display.

Going through the code I can not find something that would cause variable display size. What am I missing?

[gmaxey]

Jason/Gerry,

Thanks for you comments. I personally have no use for this sort of thing, and honestly, I just cracked my skull over it for the challenge. So my next few comments are not in its defense.

With the form displayed, the document will not scroll as it seems it should using the mouse wheel. This is true even after adding a DoEvents statement.

I don't know every other interaction which may not seem right, but it seems to me that the purpose of the thing is to offer a "tip" and that there really shouldn't need to be any interaction until after the tip is displayed and dismissed?

Gerry, I couldn't replicate what you are seeing. But, I suspect it might be due to the call to the Show_HideTitleBar routine.

I discoved that I could eliminate the "wiggle" in the userform if the user wiggles the mouse in the control, if I reintroduced Jason's original bShowing test. However, in doing so when the form is displayed and I attempt to hide the title bar, the form displays with the font a little smaller and with a narrow white band at the bottom. If I add a DoEvents statement after the call to the Show_HideTitleBar routine, the form diplays normally (normal font size).

Reintroduction the bShow caused other problem. If I deliberately moved the cursor slowly (or what I would call normal speed) in and out of the control every thing seem normal. However, if I began to move the cursor (violently) in a circular motion around and in and out of the control, I began getting an RTE in the ShowForm procedure at the UserForm1.Left line of code.

Error handling seems to have prevented this.

I suppose that I am in full agreement with you Jason. It seems that we have created a rudimentary pseudo ActiveX control tool tip. If the occasional user understands its limitations then it may suffice, but I doubt that perfection is possible, or rather I know that it is with my limited abilities.

I've reattached the document with the changes discussed above.

[fumei]

Oh I have no real use for it either. More silly eye-candy IMO. And hardy getting a value return on investment of work. Like you, I look at it as more of a curiosity what-if challenge. Interesting though.

[hometech]

Hey Guys (Skipress, Fumei, Frosty, Gmaxey)

Sitting back and watching you guys work tirelessly at something "You have no real use for" is quite touching and moving. Your time, devotion, commitment and passion to transfer knowledge is really awesome. You may not have need for this but you have just helped someone who needs it.

Looking over at all your posts, i never thought that this "small" problem would require this huge amount of code, but it obviously does.

I don't really know what else to say but THANK YOU VERY MUCH (Skipress, Fumei, Frosty and Gmaxey)....
:bow:

[gmaxey]

hometech,

Speaking only for myself, if I didn't have a passion for trying to solve Word automation problems presented by other people, then I wouldn't know much about it. Not that I know that much as it is.

With the exception of typing the occasional letter or note, I don't have that much use for Word either ;-)

Good luck.

[fumei]

"With the exceptio of typing the occasional letter or note, I don't have that much use for Word either ;-)"

The reality is that for the vast majority of people our REAL needs could still be handled by Wordperfect for DOS.

[sonick19]

"With the exceptio of typing the occasional letter or note, I don't have that much use for Word either ;-)"

The reality is that for the vast majority of people our REAL needs could still be handled by Wordperfect for DOS.