Series order vs Legend Order in Charts
 

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

Series order vs Legend Order in Charts
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"INTP56" wrote in message
...
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

Series order vs Legend Order in Charts
 

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

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"INTP56" wrote in message
...
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