Dynamically Change Row Count Using SeriesCollection(n).Values

[RFleming]

(e-mail address removed) Aug 15, 9:56 am show options
Newsgroups: microsoft.public.excel.charting
From: (e-mail address removed) - Find messages by this author
Date: 15 Aug 2005 07:56:24 -0700
Local: Mon, Aug 15 2005 9:56 am
Subject: Dynamically Change Row Count Using SeriesCollection(n).Values
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

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

 

[K Dales]

The Values or XValues needs to be set by either an array or a Range object;
try this:
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
.SeriesCollection(1).Values = Sheets("Graph").Range("R4C5:R" &
ReportLineCount - 1 & "C5")
.SeriesCollection(2).Values = Sheets("Graph").Range("R4C6:R" &
ReportLineCount - 1 & "C6")
.SeriesCollection(3).Values = Sheets("Graph").Range("R4C4:R" &
ReportLineCount - 1 & "C4")

.SeriesCollection(1).XValues = Sheets("Graph").Range("R4C3:R" &
ReportLineCount - 1 & "C3")
.Refresh
' You only need to set XValues once; it should apply to all your series here

End With

 

[K Dales]

I also suggest that until you get it working you take out the On Error Resume
Next - it is hiding the errors - that is why your chart didn't seem to change
except for the title - after that each line probably errored but moved on to
the next.