PDA

View Full Version : VBA - Changing SeriesCollection.Values for Line Chart



he4dhuntr
01-15-2009, 08:40 AM
Hi all,
I'm new to the forums and am having a problem with setting the SeriesCollection Values on my chart.

I have a Bar & Line chart and am having no issue with setting the values for my two bar series, but when it comes to the line series, I get an error message:
"Unable to set the Values property of the Series class"

Here's the code I'm using. Keep in mind that series (1) and (4) are the bar series and series (2) and (3) are the lines. Also, "created", "outstanding", "backlog" and "closed" are all Ranges. I get the error message at SeriesCollection(2), and all the series already exist in the chart.

Sheet14.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(1).Values = created
ActiveChart.SeriesCollection(2).Values = outstanding
ActiveChart.SeriesCollection(3).Values = backlog
ActiveChart.SeriesCollection(4).Values = closed

My question can most likely be solved by simply letting me know how to change the Values property of a line chart series. I've tried creating a line chart in an empty Excel Workbook, recorded a macro while changing the series values and got this code:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C1:R7C1"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R2C2:R7C2"
ActiveWindow.Visible = False
Windows("Book1").Activate

Although when I run it, it gives me the same error message as above at SeriesCollection(1). Again, all the series already exist in the chart.

Thanks for any help. I'm stumped :(

Much appreciated!
Regards,

Kenneth Hobs
01-15-2009, 09:12 AM
Welcome to the Forum!

Your code is incomplete. I don't know what the values of your variables. You must use R1C1 format.

Try something along these lines:
'http://www.mrexcel.com/forum/showthread.php?t=354580
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, bc As Range, idx As Integer
Set r = Range("E23")
If Target.Address <> r.Address Then Exit Sub
idx = WorksheetFunction.Match(r, Range("B4:B19"))
Set bc = Range("B3")
Me.ChartObjects(1).Activate
With ActiveChart.SeriesCollection(1)
.Values = "=" & Me.CodeName & "!" & Range(bc.Offset(idx, 1), bc.Offset(idx, 9)).Address(True, True, xlR1C1)
.Name = "=" & Me.CodeName & "!" & bc.Offset(idx, 0).Address(True, True, xlR1C1)
End With
r.Select
End Sub

he4dhuntr
01-15-2009, 09:24 AM
My ranges for "backlog", "created", "closed" and "outstanding" are all in this format:

Set backlog = Sheet5.Range(Cells(r2 + 1, c), Cells(lastUseRow, c))

where r2, lastUseRow and c are dimmed as Long.

I'll give your code a try. Hopefully I understand it!
Thanks for the help so far!

Kenneth Hobs
01-15-2009, 10:04 AM
Notice that I set it as a string in R1C1 format. You gave it a Range. You need something like:
ActiveChart.SeriesCollection(3).Values = "=" & WorkSheets(Sheet5.Name) & "!" & backlog.Address(True, True, xlR1C1)

he4dhuntr
01-15-2009, 10:08 AM
I tried the code, although it still gives me the same error message:
"Unable to set the Values property of the Series class"

Again, I only get this message for Line Charts, not for Bars. Is there a special way to change the CollectionSeries Values for Line Charts?

Thanks again :(

Kenneth Hobs
01-15-2009, 10:14 AM
A line chart is what I used.

Without seeing your actual code and data, I can't say what went wrong. You can attach a sample xls by using the paperclip icon.

he4dhuntr
01-15-2009, 10:25 AM
I just created a new workbook with a new Line Chart. The macro for setting the series is in Module1. Macro1 is the way I tried to do it at first, Macro2 is the way I tried to do it using your code.

Both get the same error message for me... :(

Thanks for all the help so far! It's really appreciated!

Regards,

he4dhuntr
01-15-2009, 10:56 AM
I fixed the problem by changing the chart type of the series to xlColumnClustered, then changing the Series Values and then changing the chart type back to xlLine.

Thanks for the help!
I guess it's a messy/long way around it, but it works.

Cheers,

Kenneth Hobs
01-15-2009, 11:20 AM
Good deal. Charts can be finicky. It is usually a bad idea to not plot any data. Always plot at least one point.

A dynamic named range might be something that you would want to consider.

Sub Macro2()
Dim outstanding As Range
Dim backlog As Range
Range("A1").Select 'Make sure that no object is selected.
Set outstanding = ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
Set backlog = ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp))

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!" & outstanding.Address(True, True, xlR1C1)
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Values = "=Sheet1!" & backlog.Address(True, True, xlR1C1)
Range("A1").Select
End Sub

Sub CauseSeriesToBeBlank()
Dim outstanding As Range
Dim backlog As Range
Range("A1").Select 'Make sure that no object is selected.
Set outstanding = ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
Set backlog = ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp))

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Values = "="
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Values = "="
Range("A1").Select
End Sub