Excel VBA Guru's (Chart Object)

[Sforster]

Hi,

I have a spreadsheet with quite a few charts.....

What I would like to do is change the value for the chart options:

1. X-axis value
2. Name (of the series) value
3. The values for the series themselves

Here is the code that the macro recorder produced (apart from the With's)

ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveChart
        .ChartArea.Select
        .SeriesCollection(1).XValues = "=DATA!R14C1:R25C1"
        .SeriesCollection(1).Values = "=DATA!R14C4:R25C4"
        .SeriesCollection(1).Name = "=DATA!R2C4"
        .SeriesCollection(2).XValues = "=DATA!R14C1:R25C1"
        .SeriesCollection(2).Values = "=DATA!R14C5:R25C5"
        .SeriesCollection(2).Name = "=DATA!R2C5"
    end with

This works for the Xvalues but not for values and Name, the error I am receiving is the following

"Unable to set the values property of the series class"

Does anybody know why? or have any idea of other ways of changing the series values.. (The charts aren't embedded, I assume this makes a diffence i.e. whether you can use Chartobject or Charts !!)

I have even used the following

 .seriescollection(1).Name = Worksheets("DATA").Range("A1:A25")

But that didn't work either (Seems to only work if you add a series, not amending an already existing one).... There must be a way to do this !!!! (He says)

Thanks for any help
Steve

[HarryW]

I might be able to help a little...

The X-Axis value is easy enough I think. The X-Axis is usually the CategoryAxis, which is a property of the Chart I think. If you retrieve this as an object of class Axis then you can manipulate the X-Axis through it quite easily if I remember correctly.

In my experience with Excel VBA, the name of a series was defined by the labels I used in the data. Can you just do that or are you already doing that and want to change it? I often found in Excel VBA that the easiest way of changing certain things was to remove them and then re-add them with the new data, if that makes any sense.

[Sforster]

Thanks HarryW,

The X-Axis values don't seem to be a problem, the major problem is with the values for the series and the name of the series.

I agree with the deleting and re-adding but when I add a new series, it uses my first value as the series name. I want to specify both separately.

i.e.

with activechart
  .SeriesCollection(1).delete
  .Seriescollection.Add Source:=Worksheets("DATA").Range("A14:A25")
end with

works fine except as mentioned above uses the first value (in this case the value in A14 as the Series Name)

I want to be able to specify the range for the values and the cell for the Name value.

Cheers
Steve

[Manal]

Try this it might work
with activechart
.SeriesCollection(1).delete
.Seriescollection.Add Source:=Worksheets("DATA").Range("A14:A25")
.SeriesCollection.NewSeries.Name = "your name"
end with

Good luck
Manal