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