How to dynamically add lines and boxes to an Excel chart?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'd like to dynamically draw lines on Excel Charts based on the data that is displayed. For example, I would like to add a acroos the chart that would show the average value. Or, I would like to shade a region of the chart that would highlight certian conditions. I've tried using the AddLines and AddPolyLines functions but they require x and y positions relative to the top left corner of the chart. This would be ok except that I don't know how to find the x and y position of where a particular tick mark is on a chart (i.e. where is 50 on the x or y axis). Any ideas on the proper way to procede

Thanks for youe help

Trent
 
Trent -

This macro uses the data points in series 1 in the chart and draws a
shape connecting all the points in order.

Sub DrawAShape()
Dim myCht As Chart
Dim mySrs As Series
Dim Npts As Integer, Ipts As Integer
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
Xmin = myCht.Axes(1).MinimumScale
Xmax = myCht.Axes(1).MaximumScale
Ymin = myCht.Axes(2).MinimumScale
Ymax = myCht.Axes(2).MaximumScale

Set mySrs = myCht.SeriesCollection(1)
Npts = mySrs.Points.Count

Xnode = Xleft + (mySrs.XValues(Npts) - Xmin) _
* Xwidth / (Xmax - Xmin)
Ynode = Ytop + (Ymax - mySrs.Values(Npts)) _
* Yheight / (Ymax - Ymin)

With myCht.Shapes.BuildFreeform(msoEditingAuto, Xnode, Ynode)
For Ipts = 1 To Npts
Xnode = Xleft + (mySrs.XValues(Ipts) - Xmin) _
* Xwidth / (Xmax - Xmin)
Ynode = Ytop + (Ymax - mySrs.Values(Ipts)) _
* Yheight / (Ymax - Ymin)
.AddNodes msoSegmentLine, msoEditingAuto, Xnode, Ynode
Next
Set myShape = .ConvertToShape
End With

With myShape
.Fill.ForeColor.SchemeColor = 13 ' YELLOW
.Line.ForeColor.SchemeColor = 12 ' BLUE
End With

End Sub

- Jon
 
Back
Top