I have a rather odd question.
I needed to generate 'control charts' comprising limits -- 1, 2, and 3 standard deviations. That is no problem, I have VBA generating them dynamically, pulling data from a SQL server, computing sigma, auto-scaling plot, etc. I've worked with VBA before.
Now I wanted to color the band within -1 sigma and +1 sigma green behind the line graph. And the next band yellow (2 sigma band), and the final band red (3 sigma band). That took some effort; I had to map plot coordinates to chart area coordinates and compute top and height. I set the rectangle shape transparent at 50% and its width that of the chart area. So I have 5 rectangles, overlaid on plot, but not overlaid atop each other. The VBA renders these rectangles perfectly. So charts look great.
HOWEVER, I want it so when I hover mouse over points, it shows the (time/date, value) pair. I tried moving the overlaid rectangles to the back, but all it does is display a single fractional number when I hover the mouse over the points. If I move the overlaid rectangle shape so it is not overlaid with the point, I can read x,y values again when hovering mouse over points.
Is there something I'm missing or a better way to do this? I don't know the graphic object model very well, is there some way I can set the rectangle to be more passive and not interact with the plot?
Otherwise I'm left with the workaround of putting a button on screen to toggle between showing shaded bands and horizontal lines. When I render horizontal lines on the plot (series), of course, there are no shapes, and I can read (x,y) pairs when hovering mouse, however, it does not look as good.
This is running in Excel 2013. Thanks!
Also I loved the popup calendar userform I downloaded from this site recently.
I should add that when I hover over the solid fill (but transparent) rectangle, it displays the NAME of the rectangle rather than the UNDERLYING points on the chart.
ControlChartWithShadedBands.jpg
Reply With Quote
Bookmarks