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. 

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)



You may also like

April 9, 2011

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

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free 24 Excel Tips Guide

Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!