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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Denis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

On Jul 11, 8:34 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> 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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Denis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Jul 11, 8:34 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>> 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
>