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.
Let me know if you make something cool.
Download File
Details on Demand Rollover.xlsm
