September 20

Handling Rollover Clicks Without Using the Worksheet_SelectionChange Event


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. 



You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.