Dynamically Change Row Count Using SeriesCollection(n).Values
 

I have a chart that displays data in 3 columns. The amount of data
differs depending on the reports settings. Using VBA in Excel 2002 I
am using the following code.....

Private Sub Build_RealTimeGraph(ChartTitle As String)

Dim cht As Chart
Dim Temp As String

On Error Resume Next

Sheet7.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
Selection.Characters.Text = ChartTitle

With ActiveChart
Temp = "=Graph!R4C5:R" & ReportLineCount - 1 & "C5"
.SeriesCollection(1).Values = Temp
.Refresh
Temp = "=Graph!R4C6:R" & ReportLineCount - 1 & "C6"
.SeriesCollection(2).Values = Temp
.Refresh
Temp = "=Graph!R4C4:R" & ReportLineCount - 1 & "C4"
.SeriesCollection(3).Values = Temp
.Refresh

Temp = "=Graph!R4C3:R" & ReportLineCount - 1 & "C3"
.SeriesCollection(1).XValues = Temp
.Refresh
.SeriesCollection(2).XValues = Temp
.Refresh
.SeriesCollection(3).XValues = Temp
.Refresh
End With
'Set cht = ActiveChart

'With cht
' Temp = "=Graph!R4C5:R" & ReportLineCount - 1 & "C5"
' .SeriesCollection(1).Values = Temp
' Temp = "=Graph!R4C6:R" & ReportLineCount - 1 & "C6"
' .SeriesCollection(2).Values = Temp
' Temp = "=Graph!R4C4:R" & ReportLineCount - 1 & "C4"
' .SeriesCollection(3).Values = Temp
'
' Temp = "=Graph!R4C3:R" & ReportLineCount - 1 & "C3"
' .SeriesCollection(1).XValues = Temp
' .SeriesCollection(2).XValues = Temp
' .SeriesCollection(3).XValues = Temp
' .Refresh
' End With
' Set cht = Nothing
Application.ScreenUpdating = True
End Sub

As you can see I also tried to use the Chart object as well and is
commented out in the code above. The problem I am having is that the
series does not seem to change in the chart at all. If I manually
change the series to another column and rows, it stays that way even
after the code above is run. The chart's title changes though.

Thanks

Ryan