Various people have written to me asking what they should do about popups generated by the rollover method that don’t go away on their own. Essentially, the rollover method provides for an “on mouse over” style routine but not for a “on mouse out.”
The “moat” method
One method to address this is to create a “hotspot moat” around the initial hotspot zone. In the moat, you would again use rollover technique; now, it would hide any popups initiated by the original hot spot zone.
The moat method has a rather significant drawback: if you move your mouse too quickly over the moat, you might not trip the hyperlink to remove the popup. When that happens, your pop-up remains no matter what.
The Selection Change Method
My friend Robert Mundigl of ClearlyAndSimply.Com in his Bruce Springsteen Discography in Excel Dashboard noticed this drawback as well. So, he employed another trick: use the Worksheet’s Selection_Change event like you would the moat. With this technique, when the user selects anything else on the spreadsheet, the popup is always hidden.
The Timer Method
The final method – the one for which I’ve received the most requests for instruction over email – is a mechanism to hide a popup box after a few seconds. A screen cast of it appears below.
This method uses the windows Timer API to begin fading the popup after a very small amount of time. One clear advantage to this method is that it takes care of any popups when your mouse leaves the hotspot zone. However, if you pay careful attention to the screencast, you’ll see that a popup might begin fading even if your mouse remains in the hotspot zone. (You can see this when my mouse hovers over “3”… wait for it.) If you keep your mouse still, the rollover won’t get re-tripped.
So you need to do something to “retrip” it. Here’s my hack. The numbers in the hotspots are actually a reference from in Column A. See below:
So, in my rollover function, I’ve included this additional line:
This simple bit of code does nothing more than reassign the cells in Column A to the same values that they were. In the picture above, when your mouse is over the hotspot in C4, the code snippet instructs cell A4 to be reassigned the value of one. However, since cell C4 is also dependent on the value in A4, Excel treats the assignment as a change in its dependency chain. This causes the Hyperlink function to update – and this update retriggers the rollover action. Problem solved.
You can download the timer-based popup here:
Feel free to use it in your projects without attribution. But, if you really like it, I’d love to hear from you.