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:
1: Sheet1.Range("a4:a6").Cells(index, 1).Value = index
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.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
Hi there, great work! I have been looking for exactly this scenerio, I have a problem when I download the file. I am on a 64bit system and aparently the code doesn’t work under a 64bit os. Anyway to update the codes to work under 64bit?
Thanks
April
April,
Thanks for point this out. There is indeed a small change I need to make to the file. I’ll let you know in the comments when I’ve made the update (hopefully, later today).
J
If you want to try to update it yourself (just as an exercise), checkout the module code in my Excel Holiday Card: http://optionexplicitvba.com/2013/12/22/excel-holiday-card/. You’ll see there’s compiler script that essentially tests which version of Excel is running and tells Excel to either declare the the 32 or 64 bit version.
April,
I’ve made the update. You can download it from the link above, or right here:
https://drive.google.com/file/d/0B1OBNnu3ZbL0eTMySkNMSUZ4b1U/edit?usp=sharing
I know this is pretty old but I was wondering, how would one edit the shapes your using? If I go into design mode I can click the shapes but then they fade away still. I’m still new to VBA otherwise I would know what to do lol 🙂
Hello Jonathan –
I understand the problem you’re describing. First, what you need to do is disable the entire mechanism. You can do this by going into the UDF in the module. After the public function line, Public Function ShowPopup(index As Integer), add “Exit Function” (without quotes). This will disable the mechanism and allow you to do some edits. However, at this point, the shape is likely hidden. So, from on the Home tab, go to Find & Select > Selection Pane…. From on the Selection Pane, you’ll see MyLabel is listed. If you click in the grey square next MyLabel it will make the shape visible. At this point, you can select the shape with your mouse and apply whatever edits you’d like. Once complete, make sure to remove “Exit Function” from the UDF (or, at least, comment it out in case you think you might use it later).
Ahh ok cool, thank you for the quick reply! So just from looking at this, the VBA can be applied anywhere using the custom function: =IFERROR(HYPERLINK(ShowPopup(A6),A6),A6) and using a label with this formula: =Hotzone.Index , then the text in the “popup” can be put into the hyperlink formula I assume?
At least that’s what it looks like to me.. I’m just trying to think how I can play around with this lol, I was looking for an alternative to comments as the comments I use cause problems with some other VBA’s I use.
The formula and User Defined Function are what’s required to create the rollover popup mechanism. If you’re new to these, you might try the following tutorials:
http://optionexplicitvba.com/2011/04/09/how-to-create-a-rollover-effect-in-excel-execute-a-macro-when-your-mouse-is-over-a-cell/
https://www.youtube.com/watch?v=04yl-dv68Qw
http://chandoo.org/wp/2011/07/20/interactive-dashboard-using-hyperlinks/
Awesome I will take a look at both of those, thank you.
I guess the only other thing I can’t seem to get to work is how do you change the text the shows in the popup?