Automaticaly adding a trend line to a pivot chart

[Perksy_no1]

Hi there,

I've got a workbook which automatically imports so high level data from an access data base and converts it to a pivot chart (this works fine thanks from a little help from Mr Excel )

The problem I've been possed by my boss is this. Everytime he changes different field setting he wants a trend line to appear. You can set it manually after each change but it disapears if you look at differnt info.

I've recorded myself addig that line and tried to put it in a work sheet change event (unsuccessfully).

Is there a way to do this??

I've posted what code I've got so far below.

Any help would be much appreciated and keep me in the bosses good books! Thanks in adavnce Mark

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
        Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
End Sub

 

[Perksy_no1]

bump anyone??

The info chart comes from is on a tab called Pivot. if I add the following code to that tab when I change something on the pivot it works, but i cant for the life of me get it add the trend line to the chart on tab My Chart

Thanks for anyone who can take a look at this

code In Piovt tab

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "It works here why not there"
End Sub

 

[Perksy_no1]

Im getting there,

Found this thread to help with the Chart Change but I cant get the code to add the trend line

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=64>http://www.mrexcel.com/forum/showthread.php?t=83682

Private Sub Chart_Calculate()
    With ActiveChart
    
        .SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
        Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
    
    End With
End Sub

</TD></TR></TBODY></TABLE>

It just keeps coming up with "Unable to get the Add property of the Trendline classs

any ideas?

 

[Perksy_no1]

Cracked it now!

The Chart for some reason was in a stacked form so I changed it to a normal area chart and its now working fine!

heres the code if anyone else should ever need it

Private Sub Chart_Calculate()
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
        Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
End Sub