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

[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