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

[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