1. Are there example articles and spreadsheets on how to use Excel rollovers?
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
Related
Investigations in “Illegal Activities” with User Defined Functions by The Frankens Team
E90E50fx by The Frankens Team – several of the example charts use the rollover method
2. Do rollovers work in Excel 2003 and in versions previous?
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(....),"")))
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.
4. Is there a way to handle multiple rollovers on the same sheet?
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?
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?
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!
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?
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?
- 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, your faq 8 has a link that refers to this same page.can 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. 🙂
thank you, its great stuff, great find.its 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.By the way, great work!
Thanks for letting me know about that – I'll fix it right away.
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?
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
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. 🙂
Well, you could do a timer using the Timer API. That's what I use for snakey:http://www.keepandshare.com/doc/3733067/snakey-xlsm-march-28-2012-4-53-pm-37k?da=ySend 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.
I liked so much your trick to break the rules of Excel with your rollover technique that I have implemented in one of my articles for use in the art of painting freehand. With your permission I link here:
http://pedrowave.blogspot.com.es/2013/09/how-to-paint-in-excel.html
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.
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!