VB - Dynamic Chart Creation w/ Lines

[cratediggah]

Hi all,

I'm Building a scatterplot chart and need to add some dynamic lines to it, the chart is sized to window

I would like to add a line like the following:

ActiveChart.Shapes.AddLine(47.25, 256.5, 727.5, 256.5).Select
With Selection
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 1#
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Line.BeginArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.BeginArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Line.BeginArrowheadStyle = msoArrowheadNone
Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadNone
End With

the caveat is I always need to have a line created where Y=1 as well as where X = (some value which is on a reference sheet)

Is it possible to do this using the .AddLine command and Chart Size? Thanks!

 

[Jon Peltier]

I'd suggest you add a series to the chart with X and Y values you can readily compute. You can format the series to have no lines or markers, so it's out of the way for the next part, which is:

Hack into the TraceXY example on this page of my web site:

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

which will create a polygon shape that traces an XY series from first to last point. The enhancements you'll need include (a) tracing the added series, which may or may not be SeriesCollection(1), (b) adding the arrowheads, which you already have code for, and applying the colors you want.