does anyone know how to chnage a line in a line chart making one end an arrow
and making the entire line very thick (thicker than Excel allows in the drop
down box)
This can be done with a macro, by drawing a polygon shape over the line chart
series. With little explanation, the code is given below. The chart must be a line
chart with a category, not time scale, axis.
In the block of code that begins with
With myShape.Line
you can change the color, line weight, dash style, and arrow configuration of this
added polygon shape. Beware of line wrap in the posted code.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Sub FunkyLineArrow()
Dim myCht As Chart
Dim mySrs As Series
Dim Npts As Integer, Ipts As Integer
''Dim myBuilder As FreeformBuilder
Dim myShape As Shape
Dim Xnode As Double, Ynode As Double
Dim Xmin As Double, Xmax As Double
Dim Ymin As Double, Ymax As Double
Dim Xleft As Double, Ytop As Double
Dim Xwidth As Double, Yheight As Double
Set myCht = ActiveChart
Xleft = myCht.PlotArea.InsideLeft
Xwidth = myCht.PlotArea.InsideWidth
Ytop = myCht.PlotArea.InsideTop
Yheight = myCht.PlotArea.InsideHeight
Npts = 0
For Each mySrs In myCht.SeriesCollection
If mySrs.Points.Count > Npts Then
Npts = mySrs.Points.Count
End If
Next
Xmin = 1 - IIf(myCht.Axes(1).AxisBetweenCategories, 0.5, 0)
Xmax = Npts + IIf(myCht.Axes(1).AxisBetweenCategories, 0.5, 0)
Ymin = myCht.Axes(2).MinimumScale
Ymax = myCht.Axes(2).MaximumScale
Set mySrs = myCht.SeriesCollection(1)
Npts = mySrs.Points.Count
Xnode = Xleft + (1 - Xmin) * Xwidth / _
(Npts + IIf(myCht.Axes(1).AxisBetweenCategories, 1, 0) - 1)
Ynode = Ytop + (Ymax - mySrs.Values(1)) _
* Yheight / (Ymax - Ymin)
''Set myBuilder = _
myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
With myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
For Ipts = 2 To Npts
Xnode = Xleft + (Ipts - Xmin) * Xwidth / _
(Npts + IIf(myCht.Axes(1).AxisBetweenCategories, 1, 0) - 1)
Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) _
* Yheight / (Ymax - Ymin)
.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
Next
Set myShape = .ConvertToShape
End With
With myShape.Line
.ForeColor.SchemeColor = 12 ' BLUE
.Weight = xlThick
.EndArrowheadLength = msoArrowheadLong
.EndArrowheadWidth = msoArrowheadWidthMedium
.EndArrowheadStyle = msoArrowheadTriangle
.DashStyle = msoLineLongDash
End With
End Sub
_______
