trendline position using vba

**wejones** · 11-08-2011, 10:48 PM

W7, ultimate
Office 10

I am creating hundreds of charts using modified code from the following thread:

http://www.excelforum.com/excel-char...le-charts.html

I've added a trendline and displayed equation to the chart (xlscatter): however, the trendline equation often ends up overlapping the data and trendline. I tried using a select and move with mixed results since it is a relative move. Is there any way to do a more precise move relative to 0,0 or some ordinal position on the chart?

code I am using to move trendline equation:

ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
    Selection.Left = 95.77
    Selection.Top = 91.873

The problem being that the Selection.Left or Selection.Top is relative to the current position of the equation datalable.

I also assume the units are in TWIPS? At least for graphs it used to be.

**Andy Pope** · 11-09-2011, 09:51 AM

Were should it be positioned?

**wejones** · 11-09-2011, 02:40 PM

Since this is just a single plot of x,y data any open area is ok. The most consistent open area is off of the y axis and I was going to put it up about 1/2 way. I've just started working on taking the negative of the position using the position property, then the difference between some fixed location and the label position to get the amount to move the label. I'm not sure how to differentiate needing to move the label up or down yet.

**wejones** · 11-09-2011, 05:39 PM

I think the following code is a start to a solution:

 .SeriesCollection(1).Trendlines(1).DataLabel.Select
        'trend label position relative to chart edges
        moveleft = .SeriesCollection(1).Trendlines(1).DataLabel.Left
        movetop = .SeriesCollection(1).Trendlines(1).DataLabel.Top
        'shift position relative to current position
        Selection.Left = moveleft - 10
        Selection.Top = movetop - 5

**Andy Pope** · 11-10-2011, 05:10 AM

this will position top right of plot area.

activechart.SeriesCollection(1).trendlines(1).datalabel.left = activechart.PlotArea.Left+activechart.PlotArea.width
activechart.SeriesCollection(1).trendlines(1).datalabel.top= activechart.PlotArea.top

There must be enough space between the edge of the plot area and the edge of the chartarea other wise the data label in overlay the plotarea.

**wejones** · 11-11-2011, 03:46 PM

Andy:

Works well also. After trying both approaches the relative offset from the default position gave me the best number of charts that did not require additional formatting.