Consulting

Results 1 to 20 of 20

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

  1. #1
    VBAX Regular
    Joined
    Sep 2012
    Posts
    33
    Location

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

    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.

  2. #2
    VBAX Newbie
    Joined
    Dec 2012
    Posts
    1
    Location
    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...

  3. #3
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    There is no MouseOver, but MouseMove acts like a mouse over.
    [vba]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[/vba]

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

  4. #4
    VBAX Regular
    Joined
    Sep 2012
    Posts
    33
    Location
    Okay thanks guys, i'll try this out and give you my feedback soon.
    Thanks.

  5. #5
    VBAX Regular
    Joined
    Sep 2012
    Posts
    33
    Location
    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...

  6. #6
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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)

  7. #7
    VBAX Regular
    Joined
    Sep 2012
    Posts
    33
    Location
    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

  8. #8
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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:
    [VBA]
    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
    [/VBA]
    In the UserForm1 module
    NOTE: this userform needs to have ShowModal set to FALSE
    [VBA]
    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
    [/VBA]
    In the Module1 module:
    [VBA]
    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
    [/VBA]
    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.

  9. #9
    VBAX Regular
    Joined
    Sep 2012
    Posts
    33
    Location
    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.

  10. #10
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.

    [vba]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[/vba]
    Module1
    [vba]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
    [/vba]
    UserForm1
    [vba]Option Explicit
    Private Sub UserForm_Initialize()
    bShowing = True
    End Sub
    Private Sub UserForm_Terminate()
    bShowing = False
    End Sub[/vba]
    Greg

    Visit my website: http://gregmaxey.com

  11. #11
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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
    [VBA]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
    [/VBA]

    UserForm1
    [VBA]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
    [/VBA]

    Module1
    [VBA]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
    [/VBA]
    Greg

    Visit my website: http://gregmaxey.com

  12. #12
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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 .

    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

    Quote Originally Posted by 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:
    [vba]
    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
    [/vba]
    In the UserForm1 module
    NOTE: this userform needs to have ShowModal set to FALSE
    [vba]
    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
    [/vba]
    In the Module1 module:
    [vba]
    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
    [/vba]
    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.
    Attached Files Attached Files
    Last edited by gmaxey; 12-13-2012 at 01:38 PM. Reason: Add file
    Greg

    Visit my website: http://gregmaxey.com

  13. #13
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    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!

  14. #14
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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?

  15. #15
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.
    Attached Files Attached Files
    Greg

    Visit my website: http://gregmaxey.com

  16. #16
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    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.

  17. #17
    VBAX Regular
    Joined
    Sep 2012
    Posts
    33
    Location
    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)....

  18. #18
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.
    Last edited by gmaxey; 12-17-2012 at 05:42 AM.
    Greg

    Visit my website: http://gregmaxey.com

  19. #19
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    "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.

  20. #20
    VBAX Newbie
    Joined
    Jul 2016
    Posts
    1
    Location

    Talking

    Quote Originally Posted by fumei View Post
    "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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •