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:
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:
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.
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