Series Collection Name Problem
 

I'm really hoping someone can help me here, because this is driving me
round the bend. I've got a subroutine which I call to create a chart.
Basically the code it is called from is in a loop, each time I go round
the loop the task number increments and I create a new chart for the
task.

This works the first two times and I have perfect charts for task 1 and
for task 2, but everytime task 3 falls over, with the error message
that the name cannot be set for the series collection, the series
collection is 3. There is no other collection with same name and no
other task with the same name, so I can't see what would be the clash,
the worksheets are all based on templates and are all the same, I've
tried them with different data in them and with the same data in them,
nothing seems to make a difference.

Please can anyone help I have run out of why this could be???

I've inserted the code below:


Public Sub Create_Charts()
'This function creates an EV chart for the current task

Sheets(task_sheet_name & task).Select
'could be any range, but for some reason works best if it is a blank
cell
'do need to have it?
Range("A3").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(task_sheet_name &
task).Range("A3")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(1).Values = Worksheets(task_sheet_name &
task).Range(o_Planned_EV_Col & o_Start_Row & ":" & o_Planned_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(1).Name = Worksheets(task_sheet_name &
task).Range(o_Planned_EV_Col & o_Start_Row - 1)

ActiveChart.SeriesCollection(2).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(2).Values = Worksheets(task_sheet_name &
task).Range(o_Outlook_EV_Col & o_Start_Row & ":" & o_Outlook_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(2).Name = Worksheets(task_sheet_name &
task).Range(o_Outlook_EV_Col & o_Start_Row - 1)

ActiveChart.SeriesCollection(3).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(3).Values = Worksheets(task_sheet_name &
task).Range(o_Actual_EV_Col & o_Start_Row & ":" & o_Actual_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(3).Name = Worksheets(task_sheet_name &
task).Range(o_Actual_EV_Col & o_Start_Row - 1)

ActiveChart.Location Whe=xlLocationAsNewSheet, Name:=task_chart_name
& task
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = chart_title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
chart_x_legend
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
chart_y_legend
End With
Sheets(task_sheet_name & task).Select
End Sub

Series Collection Name Problem
 

Is there valid numerical data in the .Values range for series 3? If not, VBA
cannot access certain properties of the series (such as .Name) for certain
chart types (XY and Line). Your options:

1. apply the series name before the values
2. apply a different chart type (Area) until all data has been assigned,
then change back to line

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"madeleine" wrote in message
ups.com...
I'm really hoping someone can help me here, because this is driving me
round the bend. I've got a subroutine which I call to create a chart.
Basically the code it is called from is in a loop, each time I go round
the loop the task number increments and I create a new chart for the
task.

This works the first two times and I have perfect charts for task 1 and
for task 2, but everytime task 3 falls over, with the error message
that the name cannot be set for the series collection, the series
collection is 3. There is no other collection with same name and no
other task with the same name, so I can't see what would be the clash,
the worksheets are all based on templates and are all the same, I've
tried them with different data in them and with the same data in them,
nothing seems to make a difference.

Please can anyone help I have run out of why this could be???

I've inserted the code below:


Public Sub Create_Charts()
'This function creates an EV chart for the current task

Sheets(task_sheet_name & task).Select
'could be any range, but for some reason works best if it is a blank
cell
'do need to have it?
Range("A3").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(task_sheet_name &
task).Range("A3")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(1).Values = Worksheets(task_sheet_name &
task).Range(o_Planned_EV_Col & o_Start_Row & ":" & o_Planned_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(1).Name = Worksheets(task_sheet_name &
task).Range(o_Planned_EV_Col & o_Start_Row - 1)

ActiveChart.SeriesCollection(2).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(2).Values = Worksheets(task_sheet_name &
task).Range(o_Outlook_EV_Col & o_Start_Row & ":" & o_Outlook_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(2).Name = Worksheets(task_sheet_name &
task).Range(o_Outlook_EV_Col & o_Start_Row - 1)

ActiveChart.SeriesCollection(3).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(3).Values = Worksheets(task_sheet_name &
task).Range(o_Actual_EV_Col & o_Start_Row & ":" & o_Actual_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(3).Name = Worksheets(task_sheet_name &
task).Range(o_Actual_EV_Col & o_Start_Row - 1)

ActiveChart.Location Whe=xlLocationAsNewSheet, Name:=task_chart_name
& task
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = chart_title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
chart_x_legend
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
chart_y_legend
End With
Sheets(task_sheet_name & task).Select
End Sub

Series Collection Name Problem
 

Excellent, thanks Jon. I moved the code around such that I applied the
series name before the values and all went well, very much appreciated.

Thanks again

Madeleine
Jon Peltier wrote:
Is there valid numerical data in the .Values range for series 3? If not, VBA
cannot access certain properties of the series (such as .Name) for certain
chart types (XY and Line). Your options:

1. apply the series name before the values
2. apply a different chart type (Area) until all data has been assigned,
then change back to line

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"madeleine" wrote in message
ups.com...
I'm really hoping someone can help me here, because this is driving me
round the bend. I've got a subroutine which I call to create a chart.
Basically the code it is called from is in a loop, each time I go round
the loop the task number increments and I create a new chart for the
task.

This works the first two times and I have perfect charts for task 1 and
for task 2, but everytime task 3 falls over, with the error message
that the name cannot be set for the series collection, the series
collection is 3. There is no other collection with same name and no
other task with the same name, so I can't see what would be the clash,
the worksheets are all based on templates and are all the same, I've
tried them with different data in them and with the same data in them,
nothing seems to make a difference.

Please can anyone help I have run out of why this could be???

I've inserted the code below:


Public Sub Create_Charts()
'This function creates an EV chart for the current task

Sheets(task_sheet_name & task).Select
'could be any range, but for some reason works best if it is a blank
cell
'do need to have it?
Range("A3").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(task_sheet_name &
task).Range("A3")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(1).Values = Worksheets(task_sheet_name &
task).Range(o_Planned_EV_Col & o_Start_Row & ":" & o_Planned_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(1).Name = Worksheets(task_sheet_name &
task).Range(o_Planned_EV_Col & o_Start_Row - 1)

ActiveChart.SeriesCollection(2).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(2).Values = Worksheets(task_sheet_name &
task).Range(o_Outlook_EV_Col & o_Start_Row & ":" & o_Outlook_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(2).Name = Worksheets(task_sheet_name &
task).Range(o_Outlook_EV_Col & o_Start_Row - 1)

ActiveChart.SeriesCollection(3).XValues = Worksheets(task_sheet_name &
task).Range(o_Date_Col & o_Start_Row & ":" & o_Date_Col & end_task_row)
ActiveChart.SeriesCollection(3).Values = Worksheets(task_sheet_name &
task).Range(o_Actual_EV_Col & o_Start_Row & ":" & o_Actual_EV_Col &
end_task_row)
ActiveChart.SeriesCollection(3).Name = Worksheets(task_sheet_name &
task).Range(o_Actual_EV_Col & o_Start_Row - 1)

ActiveChart.Location Whe=xlLocationAsNewSheet, Name:=task_chart_name
& task
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = chart_title
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
chart_x_legend
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
chart_y_legend
End With
Sheets(task_sheet_name & task).Select
End Sub