Adding timelines to a chart
 

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

Adding timelines to a chart
 

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" wrote in message
ups.com...
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

Adding timelines to a chart
 

On Jul 11, 8:34 am, "Jon Peltier"
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

Adding timelines to a chart
 

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" wrote in message
ups.com...
On Jul 11, 8:34 am, "Jon Peltier"
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