| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SeriesCollection Values Property Oddity | Charts and Charting in Excel | |||
| Dynamically Change Row Count Using SeriesCollection(n).Values | Charts and Charting in Excel | |||
| Cannot change SeriesCollection.Values! Why not? | Excel Programming | |||
| Setting SeriesCollection values in Excel 2000 | Excel Programming | |||
| Problem with setting values for SeriesCollection | Excel Programming | |||
Linear Mode
