Hi,

I have the following VBA code for Excel 97.

I have also one problem. When I use
ActiveChart.SeriesCollection.NewSeries, Excel will add a new legend
entry. When I use Trendlines.Add, Excel will add another one. How can I
add only the second one?

And the code...


ActiveSheet.ChartObjects("Chart 1").Activate

'Delete old data series (maybe this could be done easier?)
For i = 20 To 1 Step -1
Err.Clear
ActiveChart.SeriesCollection(i).Delete
Err.Clear
Next i

Err.Clear

'Columns 1-20
For i = 1 To 20
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).XValues = "=Sheet1!R2C1:R20C1"
ActiveChart.SeriesCollection(i).Values = "=Sheet1!R2C" & i + 1 &
":R20C" & i + 1
ActiveChart.SeriesCollection(i).Name = "=Sheet1!R1C" & i + 1

'Hide diagram
ActiveChart.SeriesCollection(i).Select

With Selection.Border
.Weight = xlThick
.LineStyle = xlNone
End With

With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With

'Add trendline
ActiveChart.SeriesCollection(i).Select
ActiveChart.SeriesCollection(i).Trendlines.Add(Typ e:=xlMovingAvg,
Period:=144, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select

ActiveChart.SeriesCollection(i).Trendlines(1).Sele ct

With Selection.Border
.ColorIndex = 40 + i
.Weight = xlThick
.LineStyle = xlContinuous
End With

With Selection
.Type = xlMovingAvg
.Period = 200
.Forward = 0
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = False
.DisplayRSquared = False
.Name = InputBox("String:", "Name of serie " & i,
ActiveChart.SeriesCollection(i).Name)
End With
Next i

Hello all,

How can I prevent Excel from adding the first legend?

ActiveSheet.ChartObjects("Chart 1").Activate

'Add serie and legend
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R20C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R20C2"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C2"

'Add trendline and legend
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlMovingAvg,
Period:=144, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select

Thanks,
Mika

Mika -

Assume you're starting with a blank chart, so the legend entry you're
removing is the first:

activechart.Legend.LegendEntries(1).delete

BTW, this line is unnecessary:

ActiveChart.SeriesCollection(1).Select

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

Mika wrote:

Hello all,

How can I prevent Excel from adding the first legend?

ActiveSheet.ChartObjects("Chart 1").Activate

'Add serie and legend
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R20C1"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R20C2"
ActiveChart.SeriesCollection(1).Name = "=Sheet1!R1C2"

'Add trendline and legend
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlMovingAvg,
Period:=144, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:=False).Select

Thanks,
Mika