Everyone! We've moved to WordPress.

Recommendation: Excel School + Excel Hero Academy Bundle

0
Chandoo (of Chandoo.org) and Daniel Ferry (of ExcelHero.com) are kicking-off their new Excel School and Excel Hero Academy. If you really want to learn Excel—and I mean learn Excel—I recommend their new courses.

It’s true; I’ve never taken a course from either of them. However, much of what you see on this website is a direct result of their work, their tutorials, their fearless trailblazing into Excel’s unknown. All of my work (not just the stuff on this blog, but my professional work too) owes a great debt to both Chandoo.org and ExcelHero.com. I can only imagine the great work they do online is expanded in their classes. In all honesty, I would sign up too if I wasn’t so pressed for time in the short term. But if you have an interest in learning Excel and taking it to the next level, I can’t think of two better people to teach you how. Believe me.

I’m not a paid spokesperson. I won’t receive anything for referring you. In fact, unless Chandoo or Daniel views this blog article (here’s hoping I’m important enough for them to follow!), they won’t even know I’ve made a recommendation. But I believe that strongly in their work.

So sign up for the Excel School & Excel Hero Academy Bundle. I think it’s well worth the money.

Handling Rollover Clicks Without Using the Worksheet_SelectionChange Event

0
You probably noticed a "bug" while playing around with my Interactive Periodic Table of Elements in Excel. When you click down into the Table of Elements (or really, anywhere on the sheet), one of the chemical classifications will become "selected," despite your not having clicked on it directly. This happens because I've used the ScrollArea property to set the clickable bounds of the spreadsheet to just the range containing the chemical classifications. When you click on one of those classifications, Excel handles the "click" through the Worksheet_SelectionChange event. So even if you don't click into any part of the clickable area defined by the Worksheet's ScrollArea property, Excel can still fire the Worksheet_SelectionChange event.

And then there's this other bug, too: If you try to click on one of those classifications more than once (without clicking onto something else first), you'll notice that nothing happens. This is because your "selection" hasn't changed (that black selector is still in the same spot).

Such are the problems when using the Worksheet_SelectionChange to handle rollover interactions. So the fix? Don't use the Worksheet_SelectionChange.

As Chandoo demonstrates in his One race, Every medalist ever – Interactive Excel Visualization article, the rollover UDF can take ranges as input parameters. Take a look at this sample UDF below:

Public Function RolloverSelection(Index As Integer, curRange As Range)
'....
 
End Function


Notice that the second parameter will take a range as its argument. That means on my Excel spreadsheet, in cell D5, I could write something like this cell for my rollover formula:

=IFERROR(HYPERLINK(RolloverSelection(2, D5),4),4)

See that highlighted portion? I'm just passing in a reference to the cell that holds the formula.

Ok, so where am I going with all of this? Well, remember that whenever we click on a "clickable" cell (that is, a cell allowed within the desired ScrollArea), the black selector changes. But how then do we find if the the selector has changed its location? We use the Selection object, of course! Thus, to test if the user has clicked onto one of our rollovers, we simply test if the address of the curRange (that's D5 above) is the same as the address of the selector. In other words:

Public Function RolloverSelection(Index As Integer, curRange As Range)

      'Do other stuff here
   
      If curRange.Address = Selection.Address Then

              'Handle clicks inside of here

      End If

End Function


Using the above function, we are able to fix both problems described that stem from using the Worksheet_SelectionChange event. In addition, we provide an even more robust mechanism for handling user clicks.

***

So here's the funny story surrounding this post. I was updating a dashboard I had worked on previously this year. As I was trying to become once again acclimated to my previous work, I couldn't understand how my code was handling user clicks. There was no code in my Worksheet_SelectionChange. So after some investigation, I saw the code above and it all came back to me. But holy crap, I must have been on something. I didn't even remember to use it for the Periodic Table of Elements. Sheesh. 

Investigations in "Illegal Activities" with User Defined Functions

0
A group of fellow Excel experts who call themselves the Frankens Team were encouraged by the rollover method to investigate other known instances in which Excel has allowed for the impossible; namely, allowing UDFs to change the value of other cells.

I invite everyone to view their terrific analysis: Excel formula to change the value of another cell?

Also, take a look around the site (which is mostly in Italian - so get that Google translator ready!) for some great Excel stuff, like Creative and advanced chart design in Excel.

By the way, what would you call this type of chart?

Courtesy E90E50 fx
:)

The Excel Rollover Mini FAQ

26
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 list: 
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
Guest Post: Rollover for Months and Years by Bert van Zandbergen (klvzndbrgn (at) gmail )
Dynamic Funnel Chart by Bert van Zandbergen
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.

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.