Alright, so this seemed like the next logical step for the rollover method:
This one is kinda complicated, I admit. Unfortunately, I didn't really take the time to clean up the spreadsheet file for others to follow (I don't really have the time these days). Sorry. But try to take it apart - and ask me questions if you have them.
I've canvassed some other folks from the Excel community to see if they would want to do a video tutorial of this - and I think that's what it would take.
So, as usual, I've been experimenting more with the rollover technique. Last night, I started experimenting with adding the capability to rollover a point on a chart (instead of just a worksheet cell) to see if I could have a label appear - or just some event fire - upon the mouse entering a chart's datapoint's "hotspot." The goal was to improve upon previous attempts to provide details-on-demand for charts. Such attempts usually required that you create a reference to a chart object and use chart sheets. Personally, I don't like chart sheets.
In the end, I moved beyond just firing an event when your mouse hovers over a data point; instead, I created the functionality to select a rectangle of data points to display information about them. See:
Because I'm now writing a book, I don't really have time to go through what I did at length. But here's a short summary.
1. I first made every cell into a square of the same size, which you can learn how to do by reading my "Most Squares Method."
2. With each cell the same size, I now had a grid that I could turn into"hot spots" for the mouse rollover. I laid out my chart to use 36 (0 - 35) squares horizontally and 17 (0 - 16) vertically. There was really no good reason for why I picked these numbers; my choice was pretty arbitrary. However, for this method, the more squares you use, the more hotspots you create; thus, more squares means more precision.
3. I then created a mouse rollover technique to capture the row and column numbers set above. Using these numbers, I mapped them on to the charts grid (so for row two, I would do 2/17 * y-axis value to get an approximate mapping). With these mapping I could approximate where the hotspots would light up certain points that were within them
4. I didn't want the user to be able to click on the graph and change its values. So I took a shape and placed it on top of the graph and assigned it to fire a macro on click. You can't see the shape because the fill color is fully transparent. Reread that last sentence and note that I did not say I used "no fill color." When you assign a macro to a shape, if you select "no fill," Excel lets you select anything that is contained by the shape as if it's not there. That would mean the user would be able to select the graph, which is exactly what I didn't want. For the shape above, I simply set its transparency to 100%.
5. Finally, to make a long story short, the shape when clicked fires the macro that allows the user to draw the rectangle.
I know right now my work isn't perfect (some data points aren't selectable for example depending upon where you start the rectangle), but I like what I have now and don't have much more time to work on it. I'll leave it to you to put it to good use.
A big thank you to everyone! This blog has achieved over 20,000 page views, which is quite an accomplishment for this humble blogger. Looking through my work, I realize that I've never formally introduced myself.
So, here goes.
My name is Jordan Goldmeier and I live in Dayton, Ohio. I work for a small start-up consulting company, The Perduco Group, developing analytical applications many of them in Microsoft Excel. Later this year, I am getting married to wonderful, incredibly smart theology student named Katherine. I'm also currently writing a book on Excel dashboards for Apress publishing.
Also, I have two cats:
Rufus
and
Theo
And then there's Katherine's dog, Katie:
***
As I went through the information Google Analytics collects about my blog, I saw people from all over the world have visited my blog. Wherever you're from - don't be a stranger - introduce yourself! And if you're in the Dayton area and haven't said "hi" yet - here's your chance!
jpo645 (at) gmail (.com)
Look forward to hearing from you. Comment, email - let's connect.
I am an enthousiast reader of the blog 'Option Explicit'. I changed the formula (see below) and made a version for showing Years and Months. Now you can use this tool for Management Information. This fantastic tool, combined with Named Dynamic Ranges gives the ultimate solution for making awesome interactive dynamic Excel charts. As soon as possible I will show you an example.
More people are interested in Excel mouse rollovers, which I think is great. I'm especially enthusiastic about Chandoo's latest dashboard contest where I found out that several contestants used the technique! By the way, there are many great dashboards showcased in the contest. Take a look and make sure to vote!
So here's the tip. If you're familiar with the rollover method - and you should be by now, it's like all I ever blog about these days - you may have noticed that while your mouse is over a cell, Excel is continuously firing the rollover method. For example, let's take a look at this snippet from Chandoo's blog:
Public Function highlightSeries(seriesName As Range) Range("valSelOption") = seriesName.Value End Function
If you place your mouse over the cell with the HYPERLINK formula that calls this function, the range valSelOption will be continuously written to. This becomes a problem when there's a lot of complex interaction on your spreadsheet. If, for example, you've used a lot of volatile functions (like OFFSET or VLOOKUP), continuously writing to the sheet will mean a recalculation for each cell with the volatile function. Nobody likes a slow spreadsheet.
The incredibly simple fix to our problem comes from the wonderful book, Professional Excel Development, by Bullen, Bovey, and Green in their chapter on spreadsheet optimization and speed tricks. Simply test if you're rewriting the same value over and over again:
Public Function highlightSeries(seriesName As Range) If Range("valSelOption") <> seriesName.Value Then Range("valSelOption") = seriesName.Value End Function
In the above code, I test if valSelOption already equals seriesName.Value. If it does, then we do nothing; if not, we write to it so that the next time the function is called with the same value in its argument (which is unpreventable if your mouse is over a cell for even a brief moment second), we can again, relax and do nothing.
Thats it! You'll likely see an immediate speed improvement, especially if you're doing lots of complex stuff with your rollover, like a this.
You may have noticed that I tend to update this blog less frequently than some of the more prominent Excel and VBA bloggers. The reason is that I blog on the side rather than full time. In fact, I work for a wonderful startup consulting company called The Perduco Group. Here’s what we’re about:
Perduco is Latin for "to lead through" or "leadership.” The Perduco Group leverages a number of key competencies in data structures, programming, operations research, and business intelligence to provide an overall analytical solution to the customer − delivering performance from data to decision. The fundamental goal is to provide a useable product founded in credible analytics to support organizations and change the way by which they do business. The Perduco Group is focused on providing organizations value by leveraging our technical skills and industrial and defense experience with our client-focused, results-oriented delivery approach.
If you’re interested in taking your spreadsheets to the next level, optimizing your efficiency with operations research, or implementing a new BI system drop me line at my work email, Jordan.Goldmeier (@) ThePerducoGroup (.com) and I can let you know what we’re all about.
As a follow-up to the above, I am gathering resumes on folks with experience in Excel, VBA, data analysis, and data visualization. If you’re interested in this type of work, feel free to send me your resume either at the email above or my personal one at the bottom of this page. The only condition is that you must be a US citizen.
