unable to get the trendlines property of the series class

[Mike C]

I'm stumped. I have a graph on a report. The graph is essentially made by
vba. I recently added a trendline in VBA so that I can start extracting
values from the trendlines anytime a particular company's series shows up on
the graph. It works great!! However, I just pasted the SAME EXACT code into
another chart in the SAME EXACT database that has the SAME EXACT structure
and...sigh...it doesn't work I'm getting an error that says "Run-time
error '1004': Unable to get the Trendlines property of the series class." I
tried finding discussions on this but there's next to none. The code is
below. Any help would be tremendous!! THANKS!!

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyChart As Graph.Chart
Set MyChart = Me.MyChart.Object

MyChart.Refresh
MyChart.Application.Update

With MyChart.Application.Chart.Axes(xlValue)
.MaximumScale = 1
End With

For i = 1 To MyChart.SeriesCollection.Count
Set mySheet = MyChart.Application.DataSheet
If mySheet.Cells(1, i + 1) = "ACME CORP" Then
MyChart.SeriesCollection(i).MarkerBackgroundColorIndex = 2
MyChart.SeriesCollection(i).MarkerForegroundColorIndex = 46
MyChart.SeriesCollection(i).Border.LineStyle = chtline
MyChart.SeriesCollection(i).Border.ColorIndex = 46
MyChart.SeriesCollection(i).Border.Weight = xlThick
MyChart.SeriesCollection(i).MarkerStyle = xlMarkerStyleTriangle
MyChart.SeriesCollection(i).MarkerSize = 8
ElseIf mySheet.Cells(1, i + 1) = "OTHER CORP" Then
MyChart.SeriesCollection(i).MarkerBackgroundColorIndex = 2
MyChart.SeriesCollection(i).MarkerForegroundColorIndex = 11
MyChart.SeriesCollection(i).Border.LineStyle = chtline
MyChart.SeriesCollection(i).Border.ColorIndex = 11
MyChart.SeriesCollection(i).Border.Weight = xlThick
MyChart.SeriesCollection(i).MarkerStyle = xlMarkerStyleCircle
MyChart.SeriesCollection(i).MarkerSize = 8

MyChart.SeriesCollection(i).Trendlines.ADD Type:=xlLinear,
Name:="L2 Linear Trend"
MyChart.SeriesCollection(i).Trendlines(i).DisplayRSquared = True
MyChart.SeriesCollection(i).Trendlines(i).DisplayEquation = True
MyChart.SeriesCollection(i).Trendlines(i).Border.ColorIndex = 3
MyChart.SeriesCollection(i).Trendlines(i).Border.Weight = xlThin
MyChart.SeriesCollection(i).Trendlines(i).Border.LineStyle = xlDot
s = MyChart.SeriesCollection(i).Trendlines(i).DataLabel.Text
M = Right(Left(s, InStr(s, "x") - 1), 4)
MyChart.SeriesCollection(i).Points(2).HasDataLabel = True
MyChart.SeriesCollection(i).Points(2).DataLabel.Text = M
Else
MyChart.SeriesCollection(i).MarkerBackgroundColorIndex = 2
MyChart.SeriesCollection(i).MarkerForegroundColorIndex = 15
MyChart.SeriesCollection(i).Border.LineStyle = chtline
MyChart.SeriesCollection(i).Border.ColorIndex = 15
MyChart.SeriesCollection(i).Border.Weight = xlThin
MyChart.SeriesCollection(i).MarkerStyle = xlMarkerStyleX
MyChart.SeriesCollection(i).MarkerSize = 4
End If
Next


End Sub

 

[BeWyched]

Hi

Trendline coding will invariably fail with Area chart types. Is this so in
your case? I couldn't see in your coding where you specify the chart type.
Perhaps your 'working' versions defaults to a line chart type, and the new
version to an area chart type. Its best to force the issue specifying the
..charttype property. - e.g. MyChart.ChartType = xlLine

Good luck

Barry