Cannot read chart object (x,y) values when hovering mouse over chart using overlaid shapes

[fseipel]

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

[p45cal]

I think you should be able to do this using a second series using a single stacked-bar chart - you would calculate the -3 sigma level for the bottom-most bar and make its fill and borders transparent, then calculate the difference between -3 and -2 sigma and plot that stacked on top of the first and colour it red, then calculate the -2 to -1 sigma difference and stack it on top of the 2nd and colour it yellow, and so on.
Supply a workbook with some sample data and your chart as it is so far, also some code if you want (since it may be easier to use your existing calculations and add the banding programmatically) and I'll have a go.

[fseipel]

p45cal: Thank you for the offer. I have done some further research on this, you are correct, stacked bar chart would work. I found a reference to doing this https://peltiertech.com/excel-charts...izontal-bands/, though I will need to convert it to an automated process (VBA) as that link is a manual process. These charts are part of a dashboard; a second sheet defines what to plot, duration, etc, so charts are destroyed and re-created each time a button is pressed, so they are all dynamic. It plots them sequentially, one above the next, using VBA.

I still like the idea of just drawing rectangles over the chart area, my working code for that is below. But on further research there appears to be no way to make tooltips/screen tips showing coordinates appear when mouse is hovered over these transparent shapes that I have drawn onto the plot with .AddShape method. The shapes themselves appear at 1, 2, and 3 sigma -- my math for calculating their Top, left, width and height was good.

I will post an answer to my own question using the technique you advise OR post a second inquiry if I can't figure it out.

I have also been experimenting with creating a 'plot.ly' plot that allows pan and zoom from within Excel (just creating an HTML file and launching the browser), that is working well for my larger time series, I have not tried same technique inside a webviewer window, but that would also be interesting to see if anybody's done it.

Code to plot horizontal bands on a chart designated chtobj:

(plots -sigma to +sigma, requires sigma, avg_value variables be set)

CH_PA_Inside_Width = chtObj.Chart.PlotArea.InsideWidth
CH_PA_Inside_Height = chtObj.Chart.PlotArea.InsideHeight
CH_PA_Inside_Left = chtObj.Chart.PlotArea.InsideLeft
CH_PA_Inside_Top = chtObj.Chart.PlotArea.InsideTop

With chtObj.Chart

Max_Y = chtObj.Chart.Axes(xlValue).MaximumScale
Min_Y = chtObj.Chart.Axes(xlValue).MinimumScale

top_coord = (Max_Y - (avg_value + sigma)) / (Max_Y - Min_Y) * CH_PA_Inside_Height ' Map ordinate/engineering unit coordinate height to Plot Area height by ratioing the two

Height = (2 * sigma) / (Max_Y - Min_Y) * CH_PA_Inside_Height ' Map ordinate/engineering unit coordinate height to Plot Area height by ratioing the two

Set shp = .Shapes.AddShape(1, CH_PA_Inside_Left, CH_PA_Inside_Top + top_coord, CH_PA_Inside_Width, Height)
shp.Fill.Transparency = 0.5 ' Fractional transparency; 0.5 = 50%
shp.Line.Visible = msoFalse
shp.Fill.ForeColor.RGB = vbGreen

End with

---

    CH_PA_Inside_Width = chtObj.Chart.PlotArea.InsideWidth
    CH_PA_Inside_Height = chtObj.Chart.PlotArea.InsideHeight
    CH_PA_Inside_Left = chtObj.Chart.PlotArea.InsideLeft
    CH_PA_Inside_Top = chtObj.Chart.PlotArea.InsideTop

With chtObj.Chart

     Max_Y = chtObj.Chart.Axes(xlValue).MaximumScale 
     Min_Y = chtObj.Chart.Axes(xlValue).MinimumScale 

     top_coord = (Max_Y - (avg_value + sigma)) / (Max_Y - Min_Y) * CH_PA_Inside_Height ' Map ordinate/engineering unit coordinate height to Plot Area height by ratioing the two
 
     Height = (2 * sigma) / (Max_Y - Min_Y) * CH_PA_Inside_Height ' Map ordinate/engineering unit coordinate height to Plot Area height by ratioing the two
     
     Set shp = .Shapes.AddShape(1, CH_PA_Inside_Left, CH_PA_Inside_Top + top_coord, CH_PA_Inside_Width, Height)
     shp.Fill.Transparency = 0.5  ' Fractional transparency; 0.5 = 50%
     shp.Line.Visible = msoFalse
     shp.Fill.ForeColor.RGB = vbGreen

End with

[fseipel]

Well, I've made considerable progress, a copy of my spreadsheet is here, truncated to not retrieve live data (it just re-plots what is on sheet when you click refresh): https://drive.google.com/open?id=1yI...8PyCZ0c1cvdPgn

So I have the color bands. HOWEVER, there is still a bit of 'whitespace' at the two edges of chart. Also, I cannot seem to set the two x-axis to the same scale, e.g. starting at a half day, one takes only 'whole dates'.

The stacked area chart itself, seems to work but I think I'm still missing a few details. I welcome any help. Ideally, I'd like to set both x-axis to same scale, both y-axis to same scale, and hide the secondary axis. I had to include a second x-axis, as otherwise dates with data at two different times, plotted at the same horizontal position as I recall.

-Frank

[p45cal]

Need permission to gain access to that file.

[fseipel]

Sorry, I neglected to turn sharing on. https://drive.google.com/open?id=1yI...8PyCZ0c1cvdPgn

Thanks,

Frank

[p45cal]

Format the lower x-axis on each chart. I've done it for the first two charts in the attached (multiple sheets and code removed to trim size of file).
2019-02-26_094458.jpg

If I get time later I'll look at your code which generates the series and try and reduce the number of series plotted as well as the number of axes on each chart.
There's quite a lot of code - could you point me to the relevant bits?

[fseipel]

Thanks much, I added this line, after the 'Skip33:' line label in Module 1, just before the Next Series instruction:

chtObj.Chart.Axes(xlCategory, xlPrimary).AxisBetweenCategories = False


This automatically positions the axis on tick marks.

Public Sub Template_Processor does the plotting.

Sorry for excess code. What this workbook does when you click the green button is the following:

1.) Processes 'Dashboard Template' one line at a time. It contains commands, described below the command list which terminates with an [END] on the last line. For instance, one Template command renders a control chart (after retrieving data for it with a SQL query), and takes parameters of title of plot, parameter to plot, duration, etc. So it goes out and does a SQL query and retrieves the data, then plots it.
2.) This is done so personnel without programming knowledge can change duration, order of plots, or add plots.
3.) The copy I sent just has EXIT SUBs for the subroutine that would retrieve data, and calculate standard deviation, because while that works for me, it would error out for you without SQL server.
4.) There are other commands to generate tabulations of data and to retrieve live data from a programmable controller (plant floor) to report instrument readings via HTTP requests. All that also works, but I disabled it in the copy I posted since it would return errors.

This workbook is truly a 'dashboard' -- it displays live data. When the 'start 5-min refresh button' is clicked, it runs a timer to automatically reload the data at 5-min intervals to display on a large TV in a control room of an operating manufacturing plant.

Also, I did have somewhat of a breakthrough; the reason I cannot set the lower axis to fractional dates to match upper secondary axis, appears to be because I have it as a 'category' axis rather than a 'value' axis.

So I think somewhere in the code, I need to set primary x-axis to value axis rather than category, then figure out how to hide one of the two horizontal axis. I will try to look up how to do that but welcome any input on the Charting object model or good references to it. I am trying to follow https://peltiertech.com/excel-charts...izontal-bands/ example of band charts but that website, while containing downloadable examples, does not present a VBA solution, only a manual procedure.

-Frank

[fseipel]

p45cal: I think I can close this thread, I added the following code to

1.) Delete lower x-axis
2.) Set upper axis to NOT show a time, e.g. it was showing 2/10/19 3:50 PM but with that addition, it now shows only whole days 2/10/19 for example above each tick mark
3.) Round down date/timestamp of leftmost x-axis and round up rightmost x-axis point so that even with whole days for tick marks, no points are cut off
4.) Always show ~15 major tick marks regardless of duration of plot (since user can plot 1 week to up to years of data)

I only tried this on the fixed dataset, I will plug into dynamic retrieval to make sure tick mark code works -- it may need some tweaking to endpoints to be Roundup or Down + or minus the TickInterval parameter, until I see what it does I can't say for sure on that minor detail, but obviously it is very easy to implement.

The only other thing I'd like to do is move the upper/secondary x-axis back to bottom of plot after deleting lower/primary one, not sure how to accomplish that but it's not a huge big deal.

In Peltier's example (non-VBA), he only creates TWO pairs to plot stacked area bars/bands across plot, so I also don't think I needed to include any intermediate points to plot the area bars, as I have done, but it works so probably won't change that!

I can post the full version if it is of use to anybody.

Thanks again for your assistance.

chtObj.Chart.Axes(xlCategory, xlPrimary).AxisBetweenCategories = False ' Set axis position on tick marks, thanks to p45cal @ Excelfox for figuring out!
chtObj.Chart.Axes(xlCategory).Delete ' Delete lower x-axis

chtObj.Chart.Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = "m/d/yyyy" ' Set x-axis to show date only, no time
' Set first/leftmost x-value on an even day (round datetimestamp down -- could also use INT function)
chtObj.Chart.Axes(xlCategory, xlSecondary).MinimumScale = Application.WorksheetFunction.RoundDown(chtObj.Cha rt.Axes(xlCategory, xlSecondary).MinimumScale, 0)
' Set second/rightmost x-value on an event day (round datetimestamp UP -- could alternately use -INT(-x) but that is harder to read)
chtObj.Chart.Axes(xlCategory, xlSecondary).MaximumScale = Application.WorksheetFunction.RoundUp(chtObj.Chart .Axes(xlCategory, xlSecondary).MaximumScale, 0)
' Calculate number of days across x-axis to determine number of tick marks (plot width will vary so this must be set progamatically)
DaysAcross = chtObj.Chart.Axes(xlCategory, xlSecondary).MaximumScale - chtObj.Chart.Axes(xlCategory, xlSecondary).MinimumScale
If DaysAcross < 15 Then ' 1-day intervals on tick marks for < 15 day plots
TickInterval = 1
ElseIf DaysAcross > 15 And DaysAcross < 32 Then ' 2 days between tick marks for 15-32 day duration plots
TickInterval = 2
ElseIf DaysAcross > 32 And DaysAcross < 60 Then ' for 32-60 day plots set tick interval to 4 days
TickInterval = 4
Else
TickInterval = Application.WorksheetFunction.RoundUp(DaysAcross / 15, 0) ' Set tick mark interval such that it provides ~15 divisons along x-axis
End If

chtObj.Chart.Axes(xlCategory, xlSecondary).MajorUnit = TickInterval
Next Series

   
   chtObj.Chart.Axes(xlCategory, xlPrimary).AxisBetweenCategories = False ' Set axis position on tick marks, thanks to p45cal @ Excelfox for figuring out!
    chtObj.Chart.Axes(xlCategory).Delete ' Delete lower x-axis

    chtObj.Chart.Axes(xlCategory, xlSecondary).TickLabels.NumberFormat = "m/d/yyyy" ' Set x-axis to show date only, no time
    ' Set first/leftmost x-value on an even day (round datetimestamp down -- could also use INT function)
    chtObj.Chart.Axes(xlCategory, xlSecondary).MinimumScale = Application.WorksheetFunction.RoundDown(chtObj.Chart.Axes(xlCategory, xlSecondary).MinimumScale, 0)
    ' Set second/rightmost x-value on an event day (round datetimestamp UP -- could alternately use -INT(-x) but that is harder to read)
    chtObj.Chart.Axes(xlCategory, xlSecondary).MaximumScale = Application.WorksheetFunction.RoundUp(chtObj.Chart.Axes(xlCategory, xlSecondary).MaximumScale, 0)
    ' Calculate number of days across x-axis to determine number of tick marks (plot width will vary so this must be set progamatically)
    DaysAcross = chtObj.Chart.Axes(xlCategory, xlSecondary).MaximumScale - chtObj.Chart.Axes(xlCategory, xlSecondary).MinimumScale
    If DaysAcross < 15 Then ' 1-day intervals on tick marks for < 15 day plots
      TickInterval = 1
    ElseIf DaysAcross > 15 And DaysAcross < 32 Then ' 2 days between tick marks for 15-32 day duration plots
      TickInterval = 2
    ElseIf DaysAcross > 32 And DaysAcross < 60 Then ' for 32-60 day plots set tick interval to 4 days
      TickInterval = 4
    Else
      TickInterval = Application.WorksheetFunction.RoundUp(DaysAcross / 15, 0) ' Set tick mark interval such that it provides ~15 divisons along x-axis
    End If
    
     chtObj.Chart.Axes(xlCategory, xlSecondary).MajorUnit = TickInterval
        Next Series