Custom VBA Tooltips for an Addon for the Excel Soccer Planner
Some of you have probably heard of our Excel Soccer World Cup 2010 Planner for the soccer world cup this year in South Africa; a conjoint project of my friends and me. I had recently announced an additional Excel VBA tool for the planner, which will, for example, be able to import and export predictions from the prediction game or send queries to a web server.
Now I had the idea to involve all interested people in the development of this tool by writing articles, how choosen features are implemented in this tool. I will also present parts of the code and at the the end of this series of articles I will write a post resuming the other posts.
This article starts with presenting a solution how to write custom tooltips using VBA. The following screenshot shows a sample of these tooltips from the options dialog in Word 2010. When you move the mouse cursor over the icons containing a small "i", a text explaining the function of the setting appears.
How can we now realize a such feature using VBA? Certainly, the simplest approach would be to insert a control to the Userform and set the "ControlTipText" property. However, these tooltips are usually yellow and provide little flexibility. So, my idea was to write two classes, which will hide and calculate the position of some static text labels. Let's first have a look on my Userform in design mode.
You can see some image controls on the left side of the Userform containing an "i". I got the image from the very interesting website www.iconfinder.net, where you can find many free images.
By the way, a small note for those who have Microsoft Visual Studio 2010 installed: do you know that the zip archive "VS2010ImageLibrary.zip" located in the folder "Installation path\Common7\VS2010ImageLibrary\1031\" contains many professional icons and symbols for use in your applications? Just unzip the file and you should find these icons organized in subfolders. If you are for example using Visual Studio 2008, you should replace "2010" by "2008" in the mentionned path above. However, as I know, the Image library is not included in the Express versions of Visual Studio.
Ok, let's go back to the Userform. I inserted three labels in a region on the Userform, which will be later invisible. The I renamed my controls to "Tooltip_Frame", "Tooltip_Title" and "Tooltip_Content". Each time you will move the mouse over one of the image controls, the three labels change their position relatively to the image control. You can trigger the mouse move event by using the "Mouse_Move" event handler.
Now, I could theoretically create this event handler for each image control in the code module of the Userform. However, this is not really flexible. To trigger this event at a global level, a class is required which also provides a variable that can handle the event. So, I created in a first step the "MLC_Tooltip_Handler" class:
'
' Copyright : 2010 by Maninweb.de - www.excelsharp.de
'
' Version : 0100
'
' Disclaimer : Feel free to use for any purpose.
' Please keep this disclaimer
'
' ........................................................................
Option Explicit
'
' Vars
'
' ........................................................................
' Control...
Public WithEvents objImage As MSForms.Image
' Tooltip...
Public objFrame As Object
Public objTitle As Object
Public objContent As Object
' Text...
Public strTitle As String
Public strContent As String
'
' Events
'
' ........................................................................
Private Sub objImage_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
' Errors...
On Error Resume Next
' Frame...
objFrame.Left = objImage.Left + objImage.Width + 2.25
objFrame.Top = objImage.Top
' Title...
objTitle.Left = objImage.Left + objImage.Width + 6.75
objTitle.Top = objImage.Top + 4.5
objTitle.Caption = strTitle
' Text...
objContent.Left = objImage.Left + objImage.Width + 6.75
objContent.Top = objTitle.Top + objTitle.Height
objContent.Caption = strContent
' Visibility...
objFrame.Visible = True
objTitle.Visible = True
objContent.Visible = True
End Sub
'
' End of file
'
' ..............................................................
At the top of the code, you can see the variable "objImage", which will be able to trigger the mouse move event. And, as you have certainly already seen, that the call also declares additional variables which will referenciate to the labels and the texts displayed by my tooltip controls. The function "objImage_MouseMove" calculates the position of my tooltip controls and unhides them.
Please note, that it would have been be possible to use global variables for the mentionned variables in the class above, if you are just using simple Userforms. In my case, I'm also using multipage controls on my Userform. If you place the tooltip controls outside a page of the multipage control, the tooltip controls will be moved behind the multipage control. The simplest way in my opinion to avoid this behaviour is to place tooltip controls on each page (where needed) of the multipage control.
In the screenshot above, I had inserted more than one image control. As the first class can only refereanciate to one control at the same time, I wrote a scond class which just manages an array of the first class. In the following the code of this class:
'
' Copyright : 2010 by Maninweb.de - www.excelsharp.de
'
' Version : 0100
'
' Disclaimer : Feel free to use for any purpose.
' Please keep this disclaimer
'
' ........................................................................
Option Explicit
'
' Vars
'
' ........................................................................
' Control...
Private objControls() As MLC_Tooltip_Handler
' Counter...
Private intCount As Long
'
' Events
'
' ........................................................................
' Initialize...
Private Sub Class_Initialize()
' Reset...
intCount = 0
End Sub
' Terminate...
Private Sub Class_Terminate()
Dim n As Long
' Bypass...
On Error Resume Next
' Check...
If intCount > 0 Then
' Loop...
For n = UBound(objControls) To LBound(objControls) Step -1
' Clear...
Set objControls(n).objContent = Nothing
Set objControls(n).objTitle = Nothing
Set objControls(n).objFrame = Nothing
Set objControls(n).objImage = Nothing
' Clear...
Set objControls(n) = Nothing
Next n
End If
End Sub
'
' Properties
'
' ........................................................................
Public Function Add(oImage As Object, _
oFrame As Object, _
oTitle As Object, _
oContent As Object, _
sTitle As String, _
sContent As String) As Long
' Bypass...
On Error Resume Next
' Check...
If intCount > 0 Then
' Redim...
ReDim Preserve objControls(intCount)
Else
' Redim...
ReDim objControls(0)
End If
' Update...
intCount = UBound(objControls) + 1
' Create...
Set objControls(UBound(objControls)) = _
New MLC_Tooltip_Handler
' Objects...
Set objControls(UBound(objControls)).objImage = oImage
Set objControls(UBound(objControls)).objFrame = oFrame
Set objControls(UBound(objControls)).objTitle = oTitle
Set objControls(UBound(objControls)).objContent = oContent
' Data...
objControls(UBound(objControls)).strTitle = sTitle
objControls(UBound(objControls)).strContent = sContent
' Return...
Add = intCount
End Function
'
' End of file
'
' ..............................................................
The class does nothing else than add elements to the array and setup the variables by using the "Add(...)" function. When unloading the class, all variables are explicitely resetted.
In order to not track and manage the texts for the tooltips in my code, I inserted the texts to an Excel sheet, as shown in the screenshot below. And I also created the column "Key", which contains exactly the names fo my controls. As you can see, I used a naming convention for the controls. All controls have a prefix followed by two words and separated by an underscore. Later, I will use this convention for implementing more features.
The code for the Userform is not very complicated. I have just to add my image controls by looping the sheet entries.
'
' Copyright : 2010 by Maninweb.de - www.excelsharp.de
'
' Version : 0100
'
' Disclaimer : Feel free to use for any purpose.
' Please keep this disclaimer
'
' ........................................................................
Option Explicit
'
' Vars
'
' ........................................................................
' Tooltips...
Private mlvhTooltips As MLC_Tooltip_Array
'
' Events
'
' ........................................................................
' Nuls...
Private Sub NUL_Background_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
Tooltip_Frame.Visible = False
Tooltip_Title.Visible = False
Tooltip_Content.Visible = False
End Sub
' Userform...
Private Sub UserForm_Initialize()
Dim n As Long
' Bypass...
On Error Resume Next
' Create...
Set mlvhTooltips = New MLC_Tooltip_Array
' With...
With ThisWorkbook.Worksheets("Tooltips")
' Loop...
For n = 1 To 12
mlvhTooltips.Add Me.Controls(.Cells(n + 7, 5).Value), _
Tooltip_Frame, Tooltip_Title, Tooltip_Content, _
.Cells(n + 7, 6).Value, .Cells(n + 7, 7).Value
Next n
End With
End Sub
'
' End of file
'
' ........................................................................
You can also find the "NUL_Background_MouseMove()" function in the Userform code module, which triggers a mouse move event for a background label (white). In fact, there is not event for triggering the mouse out event of a control, so a mouse move on the background label hides my tooltip controls. Here a screenshot of the Userfom, when running a test:
As you can see, creating a custom tooltip in Excel VBA is not really difficult. However, please note, that the code presented here is just an excerpt of the code used in the Excel Planner Tool. The code above can certainly be optimized. If you have questions regarding this code or if you would like to give me a feedback, feel free to contact me. Finally some links:
- Excel Soccer World Cup 2010 Planner, English
- Iconfinder.net, English
- Install the Visual Studio Image Library, English
This article has also been published in on my other blog Excel-Ticker.
Blog
News
