VB - Dynamic Chart Creation w/ Lines

cratediggah

New Member
Joined
Jan 11, 2006
Messages
16
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,966
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,137,366
Messages
5,681,072
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top