One of the most difficult parts of spreadsheet design relates to making complex spreadsheets accessible to all users. In business settings, a select group may be trained on how to use a spreadsheet model, but staff turnover may result in training gaps. This is where instructions embedded within a spreadsheet—such as tooltips—can play a significant role. Excel offers a few built-in tooltip solutions. This post provides an overview of a fully customizable tooltip solution using VBA.
Tooltips are an integral part of spreadsheet usability. While the spreadsheet model you build may be obvious to you, its features and functionality will not be obvious to potential users. Tooltips can help fill this gap to create intuitive and easy to understand spreadsheets. Excel offers two simple tooltip solutions:
- The “Input Message” setting within the Data Validation tool can make a textbox appear on cell selection.
- Cell comments can be embedded in a cell. When a user hovers over the cell with the cursor, a textbox will appear.
This post will provide an overview of a 3rd custom method for adding tooltips using VBA. With VBA, we can insert small shapes (that act as icons) which intuitively tell users that a tooltip exists. This approach works similarly to what you see on many web-based forms, so users should generally be familiar with their functionality.
Macros must be enabled upon opening.
The concept behind the VBA tooltips solution is quite simple. The tooltip icon is assigned to a subroutine that acts as a trigger to activate tooltip message. The subroutine then unhides a tooltip textbox (rectangle shape), arranges its coordinates near the tooltip icon, then displays a message linked to that tooltip icon.
The specific solution in this post does not present any real advantage over the built-in Data Validation tool mentioned above. However, its real advantage lies in its flexibility. While Excel’s built-in tooltips only permit the usage of static text, VBA tooltips can incorporate images, charts, and dynamic text. It also provides full control over tooltip color and design. In this post, I present a case study showing a more dynamic and interesting use of custom VBA tooltips, but we’ll stick with an easy solution for this post to make the concept clear.
Tooltip “icon” shapes can be placed next to each component which requires a tip. Generally, this would be to the right of an input box or diagram. In the example file, we use a small circle shape with a question mark — this approach is commonly found on online forms, offering users additional information on input requirements and formatting.
It’s important to include a consistent naming convention for each tooltip icon. Shapes can be named after they are right-clicked, by typing in the Name Box to the left of the formula bar (see figure to the right). In the example file, we name each tooltip icon with the convention “Tooltip_n,” with the “n” representing a unique number for each tooltip. This allows us to link the tooltip to a tailored message. It’s also important to include an underscore before the number, which will be explained in the “Tooltip Subroutines” section below. In the example file, the three tooltip icons are named “Tooltip_1,” “Tooltip_2,” and “Tooltip_3.”
In addition to the tooltip icons, a single rectangular box should be created to act as the tooltip textbox to display messages. Each tooltip icon can share the same tooltip textbox, so only one is needed per sheet (as it’s difficult to move shapes between different sheets, you should have one rectangle tooltip textbox for each worksheet that utilizes tooltips). It’s useful to incorporate a shadow format for the tooltip textbook, giving users a sense that the tooltip is floating above the spreadsheet.
We’ll speak about assigning the shapes to their proper subroutines in the next section.
The bulk of the tooltip operation is within a single subroutine. Full narrative is below the code.
Dim tooltipButtonShape As Shape
Dim tooltipMessageShape As Shape
Dim tooltipNum As Integer
Dim tooltipLeftCoord As Double
Dim tooltipTopCoord As Double
Dim tooltipText As String
Set tooltipButtonShape = ActiveSheet.Shapes(Application.Caller)
Set tooltipMessageShape = ActiveSheet.Shapes("Tooltip_Message")
tooltipNum = Split(.Name, "_")(1) ' Use split function to grab icon number.
tooltipLeftCord = .Left + .Width ' Determine the right-side coordinate of icon for textbox placement.
tooltipTopCoord = .Top ' Determine the top-side coordinate of icon for textbox placement.
Select Case tooltipNum ' Select Case statements handles tooltip text. Unique message for each tooltip icon number.
Case Is = 1
tooltipText = "Enter a stock ticker. Must be alpha characters one to four characters in length."
Case Is = 2
tooltipText = "Enter the purchase price of the stock."
Case Is = 3
tooltipText = "Enter the quantity of shares. Must be a whole number."
.Visible = True ' Make textbox visible.
.Left = tooltipLeftCord ' Assign coordinates.
.Top = tooltipTopCoord
.TextFrame.Characters.Text = tooltipText ' Update text based on Select Case statement above.
This subroutine can be assigned to each tooltip icon by (1) right clicking on each icon, (2) selecting “Assign Macro”, (3) selecting “Tooltip_Open” from the Assign Macro dialog box, then (4) pressing “OK.” Make sure to do this for each icon.
Now for the code overview. After variable declaration and setting shape object references, this subroutine consists of three parts.
First, data is extracted from the tooltip shape. The Split function extracts the unique number from the clicked tooltip icon’s name (Line 14). Then, the top and right coordinates of the tooltip icon shape are obtained (Lines 15-16). This will determine where the tooltip textbox is placed.
Second, the tooltip number is used in a
Select Case statement to link the tailored message to the tooltip textbox (lines 19-26). Each tooltip should have it’s own case within this logic structure — this example only contains 3, but can contain an unlimited number. You can learn more about
Select Case statements here.
Finally, updates are made to the tooltip textbox. This includes making it visible, updating coordinates (so it appears next to the selected tooltip icon), and updating the text-based on the
Select Case statement mentioned in the previous paragraph.
We also want to include a mechanism for closing the tooltip textbox. We do this by assigning the textbox shape to the Tooltip_Close subroutine, presented here. The subroutine assignment is made through the same method mentioned above.
ActiveSheet.Shapes("Tooltip_Message").Visible = False
This subroutine simply hides the shape.
Optional Closing Mechanisms
In the example file, the tooltip textbox is closed by clicking on the textbox. This may not be immediately obvious to the user. Another mechanism for closing a tooltip textbox could be by clicking anywhere else on the worksheet. With this behavior, the tooltip will close when a user selects the input cell to enter data. This can be done by creating a Worksheet_SelectionChange event within the Worksheet module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Shapes("Tooltip_Message").Visible = False
The script within the procedure is identical to the Tooltip_Close subroutine. This subroutine must be placed within a Worksheet module and does not need to be assigned to any shape. You can read more about Worksheet events here.
As a third alternative for a more intuitive approach, you could include an “X” shape on the top right of the tooltip textbox and assign the Tooltip_Close button that shape. This would require you to group the shapes so the “X” shape moves with the tooltip textbox.
As mentioned above, this method opens up opportunities for flexibility within tooltips. While Excel’s built-in tooltip solutions are an effective way to provide static text tooltips, custom VBA tooltips can incorporate images, charts, and dynamic text. For example, a tooltip for a stock ticker could present a miniature price performance chart, or dynamic content for financial data. Here’s a quick summary on how to do it:
- Images and charts can be incorporated by grouping charts and multiple shapes. The grouped shape can be treated similarly to a single shape by naming the grouped element.
- Dynamic text can be incorporated by concatenating content to the strings within the
Select Casestatement within the Tooltip_Open subroutine.
I’ll admit that it is possible create dynamic text content with Data Validation rules by updating validation properties in VBA, but that’s not as much fun 🙂
Make sure you check out my case study on a more robust and dynamic implementation of custom VBA tooltips.
For more tips on creating intuitive Excel dashboards and models, check out our Spreadsheet Design series.