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