Adding timelines to a chart

[Denis]

I have a bunch of line charts that plot values versus dates, where the
dates can be arbitrary dates and are not necessarily contiguous (dates
could be missing). Here's a simple code script illustrating how the
charts are set up:

With Chrt.SeriesCollection.NewSeries
.XValues = "{""07/01/2007"",""07/02/2007"",...}"
.Values = "{10,20,...}"
End With

With this approach, dates are merely a sequence of labels (I actually
use excel names rather than coding x and y values in a text string as
shown here).

Now what I would like to do is be able to draw a vertical timeline(s)
associated with key dates. So if 7/4/2007 were a key date, I would
like to indicate that on the chart. My initial thought was to just
create a new series with 2 data points, the y-axis min and max values
for the key date and the series would draw a vertical line.
Unfortunately, I can't duplicate dates in these charts so a new data
series won't work.

No doubt others have had the need to draw timelines so I'm looking for
any suggestions on how I might do that in my application.

Denis

 

[Jon Peltier]

If the chart has a time scale axis (go to Chart menu > Chart Options > Axes
tab), you can add a series as you attempted, convert the new series to XY
(select just the series, Chart menu > Chart Type), then move the new series
to the primary axis (double click > Axis tab). Repeat as needed.

- Jon

 

[Denis]

If the chart has a time scale axis (go to Chart menu > Chart Options > Axes
tab), you can add a series as you attempted, convert the new series to XY
(select just the series, Chart menu > Chart Type), then move the new series
to the primary axis (double click > Axis tab). Repeat as needed.

Unfortunately, my chart doesn't have a time scale axis. The x-axis is
merely a set of labels that happen to be dates.

However, I found a post of yours that shows how to draw a polygon on a
chart using VBA. A quick look at the VBA looks like it gives me
enough info to figure out how to simply draw a vertical line on the
chart from top to bottom. That looks like that may be the way to go.

Denis

 

[Jon Peltier]

Or use XY series, where the X is the number of categories along the X axis
(first category, use X=1; between second and third categories, use X=2.5;
etc.).

A series will stay in place relative to the axis if anything changes, while
a drawn line will stay in place relative to the chart's outer dimensions,
and drive you nuts.

- Jon