Category Archives: Rollovers

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.

Two Great Applications of Rollovers

I want to highlight two applications of the rollover technique that are really quite phenomenal.

Square Charts and Treemaps in Excel

If you haven’t seen the work of the Frankens Team yet – you’re missing out. They’re a group of…geniuses, really…who investigate and push Excel’s limit. Very recently, they wrote on the development of square charts and treemaps in Excel combined with the rollover method as shown below.

Read more about it, here. And be sure to look through the rest of their catalogue.

Excel Boilerplates

Mark Kubiszyn of Excel Boilerplate and Kubiszyn.co.uk used the technique in the development of two very slick boilerplate spreadsheets. His works adds a much needed streamlined process to creating and inserting new hyperlinks – as well, he’s expanded the ways in which you can link from hyperlinks. Please do take a look at his work, here. Also take a look at his YouTube videos on the subject.

Want to see more rollovers?

Check out the downloadable demos section of the rollover FAQ!

CUBE: The Rollover Method Returns! Spin a cube in a 3d environment with your mouse!

Ok, so I know I promised no more updates, but this one is too cool. Basically, I combined Andy Pope’s 3d rotation tutorial with the rollover method. The result is a free-floating cube which you can rotate by passing your mouse over it. Here’s a choppy-ish video of what’s going on. I promise however that it run smoothly on your computer. The choppiness is a result of the video’s frame rate.

The mechanism is similar the the election map and the details-on-demand graphs: the chart is placed on top of the hot spots to get the mouse’s coordinates. In this case, the rollover pulls in the mouse’s location and initiates a timer. The timer adjusts the rotation angles based on the mouse’s location. Additionally, some fancy calculations in the timer process procedure give the rotation a “slowing down” effect. I urge you to open on the Visual Basic Editor and do some dissection, if you desire. Remember though, the Timer API is a bit finicky. Make sure to save often in case your Excel crashes while in the middle of your investigations.

The download file: Rotate Cube Rollover.xlsm

Have fun.

Interactive United States 2012 Presidential Scoreboard in Excel

Alright, here’s another map-based rollover. The instructions are pretty simple: click on a state to toggle through its party selection. Click on one of the three selections above to change scenarios.

In the next month or so, I’ll be putting together a tutorial on this. But in the meantime, have some fun. Download. Ask questions if you need.

For more on these types of maps, checkout:
Choropleth Maps with Excel

Download here:
Election Map.xlsm

How about another screenshot?