Handling Excel Rollover Popups

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.

image

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.

popup fade

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:

image 

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.

image

You can download the timer-based popup here:

Textbox fade example2.xlsm

Feel free to use it in your projects without attribution. But, if you really like it, I’d love to hear from you.

Advertisements

11 thoughts on “Handling Excel Rollover Popups

  1. Pingback: The Excel Rollover Mini FAQ | Option Explicit VBA

  2. April Parker

    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

    Reply
    1. Jordan G Post author

      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

      Reply
  3. Jonathan

    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 🙂

    Reply
    1. Jordan G Post author

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

      Reply
  4. Jonathan

    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.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s