Dynamically Change Row Count Using SeriesCollection(n).Values
 

Aug 15, 9:56 am show options
Newsgroups: microsoft.public.excel.charting
From: - 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

Dynamically Change Row Count Using SeriesCollection(n).Values
 

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


" wrote:


Aug 15, 9:56 am show options
Newsgroups: microsoft.public.excel.charting
From: - 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

Dynamically Change Row Count Using SeriesCollection(n).Values
 

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.
--
- K Dales


"K Dales" wrote:

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


" wrote:


Aug 15, 9:56 am show options
Newsgroups: microsoft.public.excel.charting
From: - 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