Vertical Line in an XY Chart at Today's Date
 

Is there an easy way to create a vertical line in an XY chart at today's
date assuming the 'X - axis' is populated with dates? Or any other way to
easily distinguish the history from the future? It would be neat if I could
change the background color to the left and right of the now() date.

I can create the vertical line using worksheet function and by adding
another series. It's just messy in my particular spreadsheet because of
other VBA code.

Thank you,
Chris

Vertical Line in an XY Chart at Today's Date
 

Here is some code I wrote for work a few years ago.


InsideHeight = Satellite_Chart.PlotArea.InsideHeight
InsideLeft = Satellite_Chart.PlotArea.InsideLeft
InsideTop = Satellite_Chart.PlotArea.InsideTop
InsideWidth = Satellite_Chart.PlotArea.InsideWidth

LineLocation = (TodaysDate - StartDate) / (CurrentDate - StartDate)


Satellite_Chart.Shapes.AddLine(InsideLeft + (LineLocation * InsideWidth), _
InsideTop, _
InsideLeft + (LineLocation * InsideWidth), _
InsideTop + InsideHeight).Select

Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.Visible = True

"C. Bailey" wrote:

Is there an easy way to create a vertical line in an XY chart at today's
date assuming the 'X - axis' is populated with dates? Or any other way to
easily distinguish the history from the future? It would be neat if I could
change the background color to the left and right of the now() date.

I can create the vertical line using worksheet function and by adding
another series. It's just messy in my particular spreadsheet because of
other VBA code.

Thank you,
Chris

Vertical Line in an XY Chart at Today's Date
 

That line will never keep still. There are two ways to make a vertical line
that will stay where you put it. Both involve adding another series to the
chart. In one, add a series consisting of two points, both using the data
where you want it as X, the other using the minimum and maximum Y values.
Format this as an XY series with not markers but a connecting line. The
other approach needs a one-point series, where X = the date, and Y = either
the min or max, format the series to show no marker or lines, and add a
vertical error bar with a magnitude equal to the Y axis max minus min.

The techniques are as shown in this page for a column or line chart, but
easier for an XY chart:

http://peltiertech.com/Excel/Charts/AddLine.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Joel" wrote in message
...
Here is some code I wrote for work a few years ago.


InsideHeight = Satellite_Chart.PlotArea.InsideHeight
InsideLeft = Satellite_Chart.PlotArea.InsideLeft
InsideTop = Satellite_Chart.PlotArea.InsideTop
InsideWidth = Satellite_Chart.PlotArea.InsideWidth

LineLocation = (TodaysDate - StartDate) / (CurrentDate - StartDate)


Satellite_Chart.Shapes.AddLine(InsideLeft + (LineLocation *
InsideWidth), _
InsideTop, _
InsideLeft + (LineLocation *
InsideWidth), _
InsideTop + InsideHeight).Select

Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.Visible = True

"C. Bailey" wrote:

Is there an easy way to create a vertical line in an XY chart at today's
date assuming the 'X - axis' is populated with dates? Or any other way
to
easily distinguish the history from the future? It would be neat if I
could
change the background color to the left and right of the now() date.

I can create the vertical line using worksheet function and by adding
another series. It's just messy in my particular spreadsheet because of
other VBA code.

Thank you,
Chris