The Excel Rollover Mini FAQ

As more people are use the rollover method (or “Interactive Hyperlinks” as Chandoo calls them), I thought it might be helpful to answer common questions I’ve seen popping up on forums. So, in no particular order, I present the Excel Rollover Mini FAQ!

1. Are there example articles and spreadsheets on how to use Excel rollovers?

Here’s a pretty good list. I try to keep it updated.
Articles and Tutorials

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell by Me
How to: highlighting cells using the rollover technique in Excel by Me
Interactive Dashboard in Excel using Hyperlinks by Chandoo

Needs More Rollover: Quick Tip! by Me

Une macro sensible à la souris by Monsieur Excel (this one is in French!)
Handling Rollover Clicks Without Using the Worksheet_SelectionChange Event by Me
Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard by Robert Mundigl of ClearlyAndSimply.com


Downloadable Demos

How to Paint in Excel (uses rollovers) By Pedro Wave
Interactive United States 2012 Presidential Scoreboard in Excel by Me


If you know of others or would like me to add an informative article you’ve written, go ahead and email me a link.

2. Do rollovers work in Excel 2003 and in versions previous?

No, I don’t think so. I haven’t tested this for myself, but comments on other blogs suggest that the method only works for versions 2007 and 2010. Excel 2013 is not yet out, so while I think it will work in that version, we will have to wait and see for ourselves.[updated 9/21/2012]

Maybe. I might have been wrong about what I said earlier. You see, I’ve always used IFERROR to surround the HYPERLINK formula, but I forgot IFERROR didn’t exist for Excel until version 2007. This formula by Zoran Stanojević might conceivably work in Excel 2003:

=REPT("",ISERROR(HYPERLINK(getCoordinates(....),"")))

Additionally, Zoran writes:

I always avoid version specific functions when they are unnecessary because they potentially weakening the universal solution.

That’s an incredibly good point. Still, I’m probably not going to give up IFERROR anytime soon :).

But, if you are you are employing the rollover method on your own spreadsheet–and you’re concerned with backward compatibility issues–definitely consider using his formula above. Then come back and share the results with your good buddy, Jordan.

3. Why/how do rollovers work? I thought User Defined Functions could not change other values on a spreadsheet. I’ve also heard this functionality doesn’t exist in Excel.

As far as I can tell the rollover method shouldn’t exist. I think it works by taking advantage the Hyperlink formula in a way unforeseen by the original developers. Intentional or not, the functionality I believe has opened a door to some cool new possibilities with Excel.
I can only speculate why rollovers work the way they do. My best guess is that when your mouse floats over a hyperlink, Excel goes out behind the scenes to check if the hyperlink exists and loads the address in memory in anticipation of a click. This piece of memory probably goes into a generic callback procedure which knows how to handle the different actions that fill the callback queue. Because a User Defined Function is supplied instead of a URL or reference on the spreadsheet (as is normally the case when use Hyperlink) the callback function treats the UDF like any other and executes it immediately without knowing that it came from the Hyperlink formula.
I could be wrong about all of this, but that’s my (rather uneducated) guess. If any of you know better, I welcome your feedback. If I’m wrong, let me know. My feelings won’t be hurt.

4. Is there a way to handle multiple rollovers on the same sheet?

Yes. Simply create another rollover function in your module and point it to the new rollover function in your module. As far as I can tell, there is no limit to how many rollovers your spreadsheet can employ at any given time.

5. When my mouse is over the hyperlink, Excel continuously fires the macro which is slowing everything down. Is there anything I can do about this?

Yep, try this.

6. I want the entire cell to become a rollover hotspot, but the user defined function only fires when my mouse is on the text of the hyperlink – how can I make the entire cell a hotspot?

Enable wordwrap in each cell containing the rollover method.

7. My rollovers used to work perfectly, but now they don’t work at all! Recently I added some formulas like INDEX, VLOOKUP, and SUM to the hyperlink rollover formula. Now everything appears without error, but the macro no longer fires. What happened? I need to make my formulas dynamic!

Rollovers appear like regular formulas but they are not. When you add other formulas to them, they tend to not work as planned (they may work if you select the cell and hit Enter – but that’s not what you want). However, you can make your rollover functions dynamic, but you cannot do it by adding formulas directly to the rollover formula.
 
Here’s what’s I’ve found. Apparently, rollover formulas work exactly like references used for dynamic charts, dynamic labels, and form controls. Let’s take the chart example. When you want to make a dynamic chart, you must have your chart series refer to a range containing the dynamic data. You can’t place an INDEX function directly into your chart’s SERIES function, but you can have your chart point to a set of data that uses the INDEX function. You’ll have to create the same mechanism when using rollovers. So, if you want to make your hyperlink formula change its “friendly text” based something the user does, place a reference to the range with your dynamic content in the friendly text parameter of the HYPERLINK formula and the return error parameter of your IFERROR formula.
It’s really no different than what you would do when working with a dynamic chart. But unlike in charts and form controls – Excel won’t give you a warning when you’ve placed stuff into the rollover formula that shouldn’t be there – instead, your rollovers just won’t work.

8. How do I handle clicks on my Rollover cells?

You can use the Worksheet_SelectionChange event, but I prefer this method.

9. Do rollovers work on non-Windows machines, like Macs?

I don’t think so. Rollovers require Visual Basic for Applications and the latest versions of Office for Macs don’t include the ability to create or run macros, from what I’m told.
If you’re a mac user and have success using the rollover method, I’d certainly love to hear from you about it.

10. My rollover makes a popup display. How do I get rid of it?

You need to create a mechanism to remove the popup. There are several ways to do this. You could create a moat of hotspots around the original hotspot zone that removes the popup. You could also assign some code in the Selection_Change event to remove the popup. Finally, you could use a timer to remove the popup after a small amount of time.

Read more about these methods, here.

Can I add to this list?

Sure! Email me or post thoughts, examples, advice, etc.
Advertisements

17 thoughts on “The Excel Rollover Mini FAQ

  1. David Macdonald

    I discovered the site after thinking of the old mouseover/rollover technique as a possible solution to more flexible comment positioning.I already have cell selection activating macros and because these cells are around the edges of the worksheet, a lot of the comments appear offscreen. There doesn't seem to be a solution to this – you can't specify the hover position of comments. So I thought of popup textboxes and that's when I stumbled across your excellent pages (which link on to so many other interesting implementations of the basic concept…)I now have textboxes popping up exactly where I want them, each one closing all the others by deleting the named shape. But . . . the last one is left open. Without a "not rolling over any more" function, I can't close the last popup.My workaround so far is to surround the cells that activate the popups with cells containing another rollover function to delete the shape – it works but somehow I feel there should be a way to get it more "elegant".Any ideas?

    Reply
  2. Jordan Goldmeier

    If there is a more elegant way, I have yet to find it. I've encountered this same situation and have come up with two workarounds, one of which you've already discovered (that is, to create a udf to remove an item upon your mouse leaving a designated rollover hotspot). The other method – employed with finesse by my friend Robert of ClearAndSimply.Com – is to also have the item removed when a user clicks anywhere on the spreadsheet. You can do this using the worksheet_selectionchange method. Take a look at Robert's work, here: http://www.clearlyandsimply.com/clearly_and_simply/2013/07/bruce-springsteen-discography-in-excel.html

    Reply
  3. David Macdonald

    That's a shame (although I did feel a tingle of self-satisfaction when I read that "my" solution is basically the same as what you've also arrived at).I tried putting in a timer or a "wait" but I guess the continual multiple firing as long as you hover is messing those up…I plan to keep at it for a while. If I fiond something the rest of you have overlooked (which I doubt) I'll post here. 🙂

    Reply
  4. Jordan Goldmeier

    I should add though, working with the timer api is a an exercise in patience and discipline. If there's a runtime error, Excel will just crash – so it's important you save everything before testing. That's mostly easy for normal Excel applications, but with rollovers, you can accidentally activate the timer by just moving your mouse over the wrong part of the screen. It's the worst. That's why I wouldn't call it an "elegant" solution. But it is a solution you could employee-and it wouldn't be too much code either-and it looks nice when it works. Just be prepared for a potentially frustrating experience.

    Reply
  5. Pingback: Handling Excel Rollover Popups | Option Explicit VBA

    1. Jordan G Post author

      Pedro – sorry I should have added this to the list. My internet is not the best now, but I promise to add it soon! I’ve shown your paint workbook to many, many people.

      Reply
      1. pedrowave

        Jordan, it is a pleasure to know that my work paints something between Excel lovers and I’m sure you are the best ambassador presenting my Excel Painter and needless to say I give my consent for you if you want to post it on your blog. I’m sorry for my English level.

        I would be very grateful if you deign to make a comment on my blog as the author of this awesome rollover effect that allows us to do many interesting things we never thought possible without your great trick.

        Have a wonderful New Year 2014!

  6. Pingback: Making A Behaviour Flow Cosmograph - Analytics Edge Help

  7. Pingback: Introducing the CallBack() function – David Hoppe

  8. Pingback: Introducing the OnCalc() function – Excelusive

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