If you know of others or would like me to add an informative article you've written, go ahead and email me a link.
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.
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?
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.
