Category Archives: Views

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. 


One Size Fits All

Anyone who has a made a dashboard in Excel before has probably experienced this problem: a dashboard that fits nicely on your screen but not so nicely on your client’s. Yes, it’s the age old problem of differing monitors, resolutions, font-sizes and other settings that make what looks great on your screen look too small or too big on another’s. I’ve seen a few ideas to get around this problem that include resizing the spreadsheet controls to resizing the Excel window. Here’s my idea.

Most likely, your current Dashboard is already zoomed to its “best view.” By that I mean your current spreadsheet is at the proper zoom level such that your dashboard is displayed most effectively. If it’s not, go ahead and set it to the optimal zoom for your screen. Use the picture below as a guide for your dashboard and adjust the zoom levels (down in the lower-right corner) accordingly.

In this next step you’ll want to take note of the columns spanning your screen. Above, you’ll see that I’ve selected columns “A1:Z1,” but left the final column, “AA1” unselected. I would suggest highlighting all except for the last column as I did above—when we are finished, the last column will act as a nice margin. Jot down your selection; we’ll be using it in a moment.

Fire up the Visual Basic editor. Since we want our Dashboard to be in the correct view whenever someone opens the file (otherwise, what’s the use?) we’re going to add some code to be executed right when the workbook is first opened. In the upper-left hand corner of the Visual Basic Editor you’ll see a box labeled Project, which contains your Microsoft Excel objects. Double click ThisWorkbook—the workbook object belonging to our project.

In the code window, select “Workbook” from the object dropdown on the left. Then select the “Open” method from the method dropdown on the right. The Workbook_Open() procedure should appear on the screen as shown below.

Now enter the following code into your new procedure. Remember the range I said to jot down for later? Use that range in the quotes as I have for “A1:Z1.”

Now to see if our code actually works! Go back to the spreadsheet and, for testing purposes, zoom really far out to a level that your dashboard should never go. Now save and quit. (Make sure you save it as a .xlsm macro enabled workbook.)

Open your dashboard! Neat, huh? If you want to make your dashboard super snazzy, take the row selection you jotted down above and do Merge and Center on it. Now add a nice title to it for a nifty dashboard banner.

There are a few things I should mention. The user might have to hit “Enable Macros” before the code takes effect, but there’s not a ton you can do about that. And if Excel wasn’t closed correctly on a previous use, that box showing unsaved files will most likely appear and your dashboard, left with less screen real estate, will probably not zoom correctly. Finally, sometimes Excel will not open to a maximized state – but you can fix this with some VBA code.

I’ve found this to be a good workaround in my projects– but I haven’t worked with every different client computer, either. Let me know what you guys think – was this useful? How do you get around displaying the same thing on different screens?