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!
How to: highlighting cells using the rollover technique in Excel by Me
Interactive Dashboard in Excel using Hyperlinks by Chandoo
1. Are there example articles and spreadsheets on how to use Excel rollovers?
Here’s a list:
Articles and Tutorials
How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell by MeHow to: highlighting cells using the rollover technique in Excel by Me
Interactive Dashboard in Excel using Hyperlinks by Chandoo
Use a mouse hovering technique to create an interactive chart by Oscar of Get-Digital-Help.com
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
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
Guest Post: Rollover for Months and Years by Bert van Zandbergen (klvzndbrgn (at) gmail )
Dynamic Funnel Chart by Bert van Zandbergen
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:
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.
Also, I had an email conversation with someone recently who attempted to run my introduction to the rollover method file, "Snakey", in Excel 2013 preview. He said the file eventually crashed Excel 2013. However, it was the preview version, so whether the rollover method works in the final version still remains to be seen. For this blog's sake, I hope the rollover method works correctly in Excel 2013.
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?
[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.
Also, I had an email conversation with someone recently who attempted to run my introduction to the rollover method file, "Snakey", in Excel 2013 preview. He said the file eventually crashed Excel 2013. However, it was the preview version, so whether the rollover method works in the final version still remains to be seen. For this blog's sake, I hope the rollover method works correctly in Excel 2013.
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.
Can I add to this list?
Sure! Email me or post thoughts, examples, advice, etc.
hi, your faq 8 has a link that refers to this same page.
ReplyDeletecan you update it?
Thank you for pointing that out to me. The link has been updated. I hope you enjoy the Rollover method as much as I do. :)
Deletethank you, its great stuff, great find.
ReplyDeleteits a little to complex for me, i'm still new with this, but its a great challenge
Link to "Rollover for Months and Years" seems to be broken.
ReplyDeleteBy the way, great work!
Thanks for letting me know about that - I'll fix it right away.
DeleteI discovered the site after thinking of the old mouseover/rollover technique as a possible solution to more flexible comment positioning.
ReplyDeleteI 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?
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
DeleteThat'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).
ReplyDeleteI 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. :-)
Well, you could do a timer using the Timer API. That's what I use for snakey:
Deletehttp://www.keepandshare.com/doc/3733067/snakey-xlsm-march-28-2012-4-53-pm-37k?da=y
Send me an email: jpo645 (at) gmail - I have more stuff I can send you that uses the rollover method and timer API
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.
DeleteWonderful FAQ! It truly helped me. The only problem I have is that I cannot figure out how to get rid of the tooltip that reads "Click once to follow" when I hover over a cell with the Hyperlink function in it.
ReplyDeleteAny suggestions on how to hide or remove it? I tried creating a comment and setting the fill and line to no fill and no line, but the arrow remains as does the red comment indicator triangle and I'd like to keep the cell free of such distractions.
John,
DeleteWould you mind if I took a look at the file? my email is jpo645 (at) gmail. typically, that tooltip appears for normal hyperlinks that link to other cells and not hyperlinks used for rollovers. I'd be interested in seeing what you are doing on your spreadsheet.
Jordan
I'm using the hyperlink function both as a rollover effect and to link to other worksheets as demonstrated here -> http://www.excelboilerplate.co.uk/drop_down_menu.html
ReplyDeleteSo it would seem that the tooltip is being activated by the link to the other worksheets. I just can't figure out how to hide the tooltip or prevent it from showing up in the first place.
Hmmm, I hate to say this, but I'm not sure. I googled ways to fix it and came across the comment suggestion you mentioned in your earlier post. I also found a response by an Excel MVP saying that the tooltip for hyperlinks to other sheets/files are not able to be flipped off.
DeleteHi Jordan
ReplyDeleteI wonder if you can help me with adapting this technique, I've been poring over the Periodic Table file for a couple of hours now to understand how it works (which I think I do), what I want to do in a series of models I'm building is have a user-info window towards the top of the screen which would display comments based on which cell has been rolled over. But (and please correct me if I'm wrong) doesn't this technique work on indexing integers? In other words, can I make this work for text entries in cells? I don't necesariily want to have to allocate an index number to every cell in the model because that could number into 4-5 figures. Would you object if I mailed you a more detailed query please, if you can offer any advice or help on this I'd be most grateful for your time
Thank you
SG Kenny
Kenny - please do email me! jpo645 (at) gmail
DeleteThanks Jordan, mail on it's way :)
DeleteHello Jordan, just wondered if my mail arrived ok (I had a couple of connection failures on my latop at the time)?
DeleteJordan I must wanna appreciate your efforts and want to thank you from the depth of my heart for being such kind and helpful.
ReplyDeleteAdam
This comment has been removed by a blog administrator.
ReplyDeleteHi
ReplyDeleteI was wondering if you could achieve a mouse over effect using a shape (or a text box) in excel. I have a 'circle' and would like to be able to change the shading when the user runs their mouse over the shape to make it obvious that the shape is clickable.
So using shapes is a bit harder. I've actually done it, here:
Deletehttp://optionexplicitvba.com/2012/10/03/interactive-united-states-2012-presidential-scoreboard-in-excel/
And you can see a good tutorial on it here:
http://www.clearlyandsimply.com/clearly_and_simply/2012/11/roll-over-tooltips-and-web-actions-on-a-microsoft-excel-dashboard.html
Here's the basics of it. You'll need to create a "rollover range" and place your shape on top of it. When you are placing your shape on top of the range, you can use snap-to-grid to get a good fit. However, you'll still need a way to undo the rollover effects. Here's a good article on how to do just that:
http://optionexplicitvba.com/2013/09/13/handling-excel-rollover-popups/
This comment has been removed by the author.
ReplyDeleteAwesome article. Works for me on Mac Excel 2016. Thx!
ReplyDeleteOnly problem is, the rollover executes when the Workbook_Open procedure finishes. The only fix i've found is to set calculation to manual, which we can't do. Also, when calculation mode is switched from manual to automatic, the rollover executes. Any ideas? Is there a way to make a hyperlink formula NOT calculate when the rest of the sheet calculates? maybe some worksheet function i can wrap it in to stop it from calculating? Hope someone can solve it! thx
ReplyDeleteExcel creates the calculation chain when you first open the workbook. That means Excel will calculate most everything and figure out the most efficient way to drive calculations from then on out (assuming you don't make big changes to the workbook). That yours recalculates every time you open might be because you've references cells that use volatile functions (e.g indirect, offset, row, cell, now, etc). But without seeing your workbook that's just a guess.
Delete