Chart Trendline Reference

[richlocus]

Hello:
I have two worksheets that contain charts (pie, bar, etc). Worksheet 1 contains charts with NO TRENDLINES. The Worksheet 2 contains charts WITH TRENDLINES.

In Worksheet 1 I was able to get rid of any select statements so that the worksheet doesn't need to be active for me to manipulate it.
Worksheet 2, however, still requires me to activate it because of the select statements in the trendlines. I have not yet found a way to program the trendlines without the select statements. Here's the example:

' **********************************************************************************
' Add Trend Lines With Select Statements
' **********************************************************************************
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select

I would like to convert these three lines of code above to a method that does not require me to activate Worksheet 2.

All the other parameters below are working without using a select statement except the trendlines.

For example, here's the code that works except for the trendlines at the bottom, which still require the method of "selecting".

Sub CreateBarChartMRR(chartTitle As String, dblLeft As Double, dblWidth As Double, dblTop As Double, dblHeight As Double)
' ************************************************************************************
' Bar Chart for MRR 12 Month Graph - This section of code doesn't require select statements.
' ************************************************************************************
Dim wkbCommissionsReports As Workbook
Dim wksCommissionsReports As Worksheet
Dim newChart As Shape
Dim txtChartName As String
Set wkbCommissionsReports = ThisWorkbook
Set wksCommissionsReports = wkbCommissionsReports.Sheets("GraphicsReport")
Set newChart = wksCommissionsReports.Shapes.AddChart
txtChartName = newChart.Name
With newChart
.Left = dblLeft
.Width = dblWidth
.Top = dblTop
.Height = dblHeight
.Chart.ChartType = xlColumnClustered
.Chart.HasTitle = True
.Chart.chartTitle.Text = chartTitle
.Chart.HasLegend = False
.Chart.SetSourceData Source:=Worksheets("GraphicChartParameters").Range("A11:L12")
End With
' **********************************************************************************
' Add Trend Lines (I Want To Get Rid of the Select Statements)
' **********************************************************************************
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
End Sub

Thanks for any suggestions.
Rich Locus

[HansV]

Like this:

Code: Select all

Sub CreateBarChartMRR(chartTitle As String, dblLeft As Double, dblWidth As Double, dblTop As Double, dblHeight As Double)
    ' ************************************************************************************
    ' Bar Chart for MRR 12 Month Graph
    ' ************************************************************************************
    Dim wkbCommissionsReports As Workbook
    Dim wksCommissionsReports As Worksheet
    Dim newChart As Shape
    Dim txtChartName As String
    Set wkbCommissionsReports = ThisWorkbook
    Set wksCommissionsReports = wkbCommissionsReports.Sheets("GraphicsReport")
    Set newChart = wksCommissionsReports.Shapes.AddChart
    txtChartName = newChart.Name
    With newChart
        .Left = dblLeft
        .Width = dblWidth
        .Top = dblTop
        .Height = dblHeight
        With .Chart
            .ChartType = xlColumnClustered
            .HasTitle = True
            .chartTitle.Text = chartTitle
            .HasLegend = False
            .SetSourceData Source:=Worksheets("GraphicChartParameters").Range("A11:L12")
            .SeriesCollection(1).Trendlines.Add Type:=xlLinear, Forward:=0, _
                Backward:=0, DisplayEquation:=False, DisplayRSquared:=False
        End With
    End With
End Sub

[richlocus]

Thanks Hans!!