Re: Problems with charts, VBA and series...

From: Jon Peltier (jonxlmvpNO_at_SPAMpeltiertech.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 11:07:25 -0500

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

J S wrote:

> I have a chart in a work***, 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 spread*** 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
> Active***.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
>
>
>
>
>

Loading