This post provides a case study of an advanced implementation of custom VBA tooltips. While Excel offers a built-in tooltips feature for data validation, its limited to displaying simple text messages. By implementing custom tooltips through VBA, you can provide users with dynamic validation hints and infographics.
Last week, I posted a tutorial on a simple implementation of VBA tooltips. The specific solution I provided in the tutorial did not pose any real advantage over Excel’s built-in data validation tool. The purpose of the tutorial was to provide readers a simple example for creating custom tooltips in VBA. I recommend reading that post before proceeding with this tutorial.
In this post, I’ll provide an overview of an advanced implementation of custom VBA tooltips. By incorporating conditionals, grouping shapes, and providing dynamic feedback, you can make professional looking and informative tooltips.
Case Study File
Macros must be enabled upon opening.
As explained in the previous post, VBA tooltips provide flexibility beyond Excel’s built-in validate functions. In this post, we’ll explore some advanced usage of custom VBA tooltips that can provide users with valuable feedback. In the case study file, a simple stock purchase form (figure to the left) asks users to enter a stock ticker, purchase price, and purchase quantity. Upon entry of an acceptable stock ticker, an infographic will appear providing details about the stock. Here’s a summary of what users will see.
The bulk of the custom VBA tooltip functionality in this case study is built within the “Stock Ticker” field. In addition to providing validation feedback, the tooltip will provide information about a stock ticker when entered correctly.
The “Stock Ticker” field is set up to accept 5 specific stock tickers. Acceptable tickers are MSFT, AAPL, GE, EIX, or BA. If any other stock tickers (or invalid characters) are entered, the following error message appears.
One of the strongest advantages of custom VBA tooltips are their versatility. For the “Stock Ticker” field in the example file, the tooltip serves a dual purpose. First, it notifies the user of an input error and offers a suggestion for correct inputs. Second, when a correct data is inputted, it provides an infographic associated with that stock ticker.
The “Purchase Price” field can only accept a dollar value number. Any alpha characters within the field will return an error.
Similar to “Purchase Price,” the “Purchase Quantity” field requires a numeric value.
In addition to the data validation feedback and infographic, the following features are included in this custom VBA tooltips example.
- Tooltip icons are highlighted dark green when the validation message/infographic is active.
- Tooltip icon can be clicked to display message. Otherwise, message will appear if a validation error is triggered (after a user types data).
- If correct data is entered, a neutral white background will appear on the tooltip if tooltip icon is clicked.
- Infographic visuals change based on stock performance — chart data series color will be red or green.
These features are not available with Excel’s built-in data validation tool.
Shapes play a central role in this custom VBA tooltips implementation. This implementation relies heavily on grouped shapes — shapes which are closely associated and linked to one another. By grouping shapes, we can ensure that the position of two or more shapes remain constant, relative to each other. It’s also important that a “family” of grouped shapes are provided a name, allowing VBA shape properties to be applied to all shapes within the group. This is crucial in our case study file for consistent visibility and coordinate attributes.
In this post’s case study file, two shape groups exist: one for the validation tooltip; the second for the inforgraphic. The validation tooltip is a group of three images (the message box, close text, and close “X”). The infographic consists of 13 shapes and 1 chart (charts can be grouped with shapes).
To group shapes, multiple shapes must selected at the same time (Ctrl or Shift while selecting) then accessing the group option through Format > Group > Group.
Once grouped, the whole group shaped must be named within the Name Box to the left of the Formula Bar. At this point, the grouped shape will behave as if it were an individual shape.
I won’t spend too much time going over the VBA code as this case study is very specific to its implementation. The purpose of this post was to show potential options using custom VBA tooltips. Each implementation will likely be very different depending on what your goals are. However, there are a few principles which will be similar across implementations. These principles are discussed in the following sections.
Before proceeding, I recommend that you review the code in the case study. Many of the VBA concepts used are discussed in the Getting Started with VBA series.
It’s important to have consistent rules for when tooltips are active or when they will be displayed. In the example file, tooltips are activated only under the following conditions:
- Correct data is inputted into the “Stock Ticker” field, presenting an infographic.
- Incorrect data is inputted into any field, showing a validation error message.
- The user clicks on any tooltip icon, showing information about the field or the infographic.
- The infographic will disappear only if a validation error comes up in another field.
While this seems relatively straight forward, there are a few considerations. First, this approach requires two subroutines to handle the actual activation. Given that we want users to see tooltips if erroneous data is entered OR when they click the tooltip, we need to set up a
SelectionChange event subroutine in the worksheet module, and a subroutine in a standard module to handle the tooltip icon clicks.
The two tooltip handling subroutines pass arguments to the
Validate_Entry subroutine which checks if input is valid. The select case statement within this procedure will test if number fields are numeric, or if the “Stock Ticker” field contains an acceptable ticker symbol. Once validation is complete, arguments are passed to the
Tooltip_Open subroutine telling it what type of message should be displayed to help the user.
A somewhat complex procedure is called to handle the display and content of the tooltip. The
Tooltip_Open subroutine will decide on the look and content of the tooltip based on arguments passed from the validation procedure. This includes:
- Determining if a validation message or infographic should be shown.
- Updating the color of the tooltip (red for an error).
- Handling the color changes of the tooltip icons.
- Managing text content of the tooltips.
- Managing shape visibility.
This procedure also includes calls to the
Ticker_Change subroutines which are just separated out for clarity and efficiency.
The final subroutine,
Tooltip_Close, is fairly straight forward as it simply closes the tooltip. This subroutine is assigned to the shape group for each tooltip, allowing users to close a tooltip by clicking on the shape. A close “X” has also been included in the shape group as it may not be obvious to some users that clicking on the shape will close the tooltip.
In the previous tutorial showing the simple tooltips implementation, we also triggered a tooltip close if the user clicked anywhere else on the spreadsheet. From a strictly design perspective (attempting to mimic common web-based forms), there seems to be some applications of both approaches: whether or not tooltips close by clicking elsewhere. It likely depends on how critical a validation error is, or whether or not you want an infographic to continue to be displayed as the user interacts with the rest of the application.