Problems with charts, VBA and series...

[J S]

I have a chart in a worksheet, the chart is a stacked column chart with 6
series and I want to programmatically changed the names of each of the data
series. I can do this just fine if I do it manually. When I use the macro
recorder and try to run the recorded code I get the Following error: 1004
Unable to set the Name property of the Series class

I have no idea why this is the case, Interestingly enough when I use the on
error resume next, I find that I am able to change the name of the first
series and the sixth series with no problems but data series 2-5 all
generate the same error.

I am using excel 2000 on windows xp pro.

If anybody would like a sample spreadsheet that demonstrates this problem
let me. The sample code I used is pasted below.

-J

Sub Macro1()
On Error Resume Next
Sheets("Acquisition (Donor Value)").Select
ActiveSheet.ChartObjects("Chart 2").Activate

ActiveChart.SeriesCollection(1).Name = "=""a1"""
Debug.Print "#1 " & Err.Number & " " & Err.Description
Err.Clear

ActiveChart.SeriesCollection(2).Name = "=""a2"""
Debug.Print "#2 " & Err.Number & " " & Err.Description
Err.Clear

ActiveChart.SeriesCollection(3).Name = "=""a3"""
Debug.Print "#3 " & Err.Number
Err.Clear

ActiveChart.SeriesCollection(4).Name = "=""a4"""
Debug.Print "#4 " & Err.Number
Err.Clear

ActiveChart.SeriesCollection(5).Name = "=""a5"""
Debug.Print "#5 " & Err.Number
Err.Clear

ActiveChart.SeriesCollection(6).Name = "=""a6"""
Debug.Print "#6 " & Err.Number
Err.Clear
End Sub

 

[Jon Peltier]

JS -

If you arrange the data appropriately, this exercise is not necessary.
Here's a format that will use the first row as series labels and the
first column as category labels:

Srs1 Srs2 Srs3 etc
Cat1 Data goes here
Cat2 " " "
Cat3 " " "
etc

Leave the top left cell blank, select the entire range including labels
and blank cell, and make your chart.

What error do you get? Are series 2-5 actually charted, or are their
ranges completely blank or filled with non charting error values? If VBA
can't access the series formula (even if you can see it manually), you
will get an error.

Here's a little thing I use to assign values in a range to series names
in a chart:

' ======================================================================
Sub SeriesNameAssigner()
Dim oCht As Chart
Dim oSrs As Series
Dim oRng As Range, vRng
Dim iSrsCt As Integer, iSrsIx As Integer

' Define the chart
Set oCht = ActiveChart
If oCht Is Nothing Then
MsgBox "Please select a chart, then try again", _
vbOKOnly, "Select a Chart"
Exit Sub
End If

' What range contains the names
Set oRng = Application.InputBox(Prompt:= _
"Select a range of cells containing the series names.", _
Title:="Select Series Names", Type:=8)

' Check number of series and names
iSrsCt = oCht.SeriesCollection.count
If oRng.Rows.count <> 1 And oRng.Columns.count <> 1 Then
MsgBox "Range should be in a single row or column.", _
vbOKOnly, "Incorrect Range Shape"
Exit Sub
End If
If oRng.Cells.count <> iSrsCt Then
MsgBox "Range has " & oRng.Cells.count & _
" names, but Chart has " _
& iSrsCt & " Series to name.", _
vbOKOnly, "Incorrect Range Size"
Exit Sub
End If

' Apply cell contents to series names
For iSrsIx = 1 To iSrsCt
''' Need R1C1 form for address
oCht.SeriesCollection(iSrsIx).name = _
"='" & oRng.Parent.name & _
"'!R" & oRng.Cells(iSrsIx).Row _
& "C" & oRng.Cells(iSrsIx).Column
Next

Set oRng = Nothing
Set oCht = Nothing
Exit Sub

End Sub
' ======================================================================

- Jon

 

[J S]

The error I get is the following:

1004 Unable to set the Name property of the Series class

All the values in the data series do in fact exist, which is why I'm miffed.
I'll email you the spreadsheet, maybe that will shed some light on this...

-J