Series order vs Legend Order in Charts

[INTP56]

First off, Excel 2003.

OK, first some background. I'm getting variable data sets back from a
database. (Variable because what gets captured varies by station and run,
this part is out of my control) I use ADO to put the data into a worksheet,
OK.

I have a list of column headers where if they show up, I want to plot them.

I go through the column headers looking to see if they match, and if they
do, I add them as a series to the chart. In addition, if the column header
contains the word "weight", I want to plot that on a secondary axis and use
diamond markers to indicate those lines are using the secondary axis.

At this point, everything is fine, EXCEPT...

Although the order of the series I added is the order in which I added them,
the Legend Entries are not. My routine in words is

Create a new series
Assign attributes
If the name has "Weight" in it, I alter the last legend entry (Using .count)

This "works" when I ASS-U-ME the series I just added will be the last legend
entry.

In my test case, it works for the first six series I add:

FluidNumber
Pump Rate
Pump Volume
Oil Weight
Water Weight
Sand Weight

However, stepping through the code, the next .NewSeries shows up at postion
4 in the legend.

FluidNumber
Pump Rate
Pump Volume
New Series
Oil Weight
Water Weight
Sand Weight

I set Series.Name to "Sand DP" (from New Series) and since it doesn't have
the text "weight", I set LegendEntry(7) to be with no marker, which is not
what I want.

Any Ideas on what's happening or how to code this?

Thanks, Bob

 

[Jon Peltier]

Why not format the series you've just added, since you know it's
SeriesCollection(SeriesCollection.Count). The legend is in a different
order, based on axis (all primary series before all secondary series) and
also by chart type (columns before lines, etc.).

- Jon

 

[INTP56]

Jon,

Thanks. I put the new series into an object variable when I create it. I
didn't realize I could set those properties from the series, I thought I had
to do it via the LegendEntries.

Now I don't have to deal with the LegendEntries at all, which does seem
"cleaner" to me.

Bob