herm5263
06-17-2010, 12:07 PM
I wanted to write macro to add a trendline to a stacked bar chart. To see what sample code might look like, I added a trendline to a scatter plot with Macro Recorder turned on. But recorded macro errors at .Add method out even tho' the recorded actions worked fine. ("Run-time error 80004005" , "Method 'Add' of object 'Trendlines' failed.") Can't seem to make it work. Also, trendline formatting does not record. Here is approx. recorded VBA code ---
Sub AddTrendline()
' Add Trendline to chart
' Keyboard Shortcut: Ctrl+Shift+L
'
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Trendlines.Add ' Error 80004005
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection
.Type = xlPolynomial
.Order = 2
End With
With Selection
.Type = xlPolynomial
.Order = 5
End With
End Sub
Sub AddTrendline()
' Add Trendline to chart
' Keyboard Shortcut: Ctrl+Shift+L
'
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Select
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Trendlines.Add ' Error 80004005
ActiveSheet.ChartObjects("Total_Assets").Activate
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection
.Type = xlPolynomial
.Order = 2
End With
With Selection
.Type = xlPolynomial
.Order = 5
End With
End Sub