Hi there, using XL2K

I am using GetChartElement on a chart to find out which series the user has
clicked on in the legend, and I have run into a small (hopefully) stumbling
block - my chart is a clustered column chart with 2 y-axes, where series 1
and 2 are on the primary and series 3 is on the secondary.

When GetChartElement returns arg1 as the seriesindex (arg2 is presumed empty
because that's what is supposed to happen when the elementid is xlLegendKey
or xlLegendEntry, correct?), everything is okay if the series picked is one
of the two on the primary axis, but if it's series 3, then arg1 is set to
"1", not "3" as it should be, I guess because it's series 1 on the secondary
axis.

So the question that comes up, is that since Excel seems to be saying
"you've picked series 1 on the secondary axis", how do I find out whether
the user's picked series 1 on the secondary axis or the primary???


----------------------------------
Please reply to NG.

Hi,

I don't think the value being returned is the true series index.
It is a value dependent upon primary/secondary axis as you point out but
it is also dependent on chart type.

Create a simple clustered ccolumn chart with 3 series.
Using the chartevents and selecting the legend entries in turn will give
you arg1 values of 1,2 and 3. Which is what you would expect.

Now change the 2nd series to a line chart and the 3rd series to a
xy-scatter chart. Do not move any to the secondary axis just yet.
Again select the 3 legend entries. This time arg1 return 1 for all three
series.

So it would appear that the limited information returned is of no use
once a chart contains series that are on the secondary axis or is a
combination chart.

Cheers
Andy

Mike wrote:
Hi there, using XL2K

I am using GetChartElement on a chart to find out which series the user has
clicked on in the legend, and I have run into a small (hopefully) stumbling
block - my chart is a clustered column chart with 2 y-axes, where series 1
and 2 are on the primary and series 3 is on the secondary.

When GetChartElement returns arg1 as the seriesindex (arg2 is presumed empty
because that's what is supposed to happen when the elementid is xlLegendKey
or xlLegendEntry, correct?), everything is okay if the series picked is one
of the two on the primary axis, but if it's series 3, then arg1 is set to
"1", not "3" as it should be, I guess because it's series 1 on the secondary
axis.

So the question that comes up, is that since Excel seems to be saying
"you've picked series 1 on the secondary axis", how do I find out whether
the user's picked series 1 on the secondary axis or the primary???


----------------------------------
Please reply to NG.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Just to add, in theory a legend item can be related to a series with a lot
of work from both the ChartGroups collection and Axes, don't forget
Trendlines, and there's more to look at too that for the moment I don't
recall. But it all falls apart if user has deleted a legend entry !

To some extent that scenario can be catered for if the counts don't match by
cross-referencing colour formats, doesn't necessarily require a unique set
of colours but it might.

As a last resort can temporarily change a legend item's colour format to
something known to be unique, find it amongst the series, then reset (but
don't do that if any points have been individually formatted or they'll be
lost). Not nice!

Regards,
Peter T

"Andy Pope" wrote in message
...
Hi,

I don't think the value being returned is the true series index.
It is a value dependent upon primary/secondary axis as you point out but
it is also dependent on chart type.

Create a simple clustered ccolumn chart with 3 series.
Using the chartevents and selecting the legend entries in turn will give
you arg1 values of 1,2 and 3. Which is what you would expect.

Now change the 2nd series to a line chart and the 3rd series to a
xy-scatter chart. Do not move any to the secondary axis just yet.
Again select the 3 legend entries. This time arg1 return 1 for all three
series.

So it would appear that the limited information returned is of no use
once a chart contains series that are on the secondary axis or is a
combination chart.

Cheers
Andy

Mike wrote:
Hi there, using XL2K

I am using GetChartElement on a chart to find out which series the user
has
clicked on in the legend, and I have run into a small (hopefully)
stumbling
block - my chart is a clustered column chart with 2 y-axes, where series
1
and 2 are on the primary and series 3 is on the secondary.

When GetChartElement returns arg1 as the seriesindex (arg2 is presumed
empty
because that's what is supposed to happen when the elementid is
xlLegendKey
or xlLegendEntry, correct?), everything is okay if the series picked is
one
of the two on the primary axis, but if it's series 3, then arg1 is set
to
"1", not "3" as it should be, I guess because it's series 1 on the
secondary
axis.

So the question that comes up, is that since Excel seems to be saying
"you've picked series 1 on the secondary axis", how do I find out
whether
the user's picked series 1 on the secondary axis or the primary???


----------------------------------
Please reply to NG.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thanks for the info Peter & Andy.

Kind of funny Peter that you mention using colour to cross reference, as the
reason I'm using this is to allow the user to change the colour of the
series on charts that I've got "all locked up" by eating all the events
thrown at the charts.

However, it sounds like I'm better off implementing this function through a
different means.... thanks.

"Peter T" <peter_t@discussions wrote in message
...
Just to add, in theory a legend item can be related to a series with a lot
of work from both the ChartGroups collection and Axes, don't forget
Trendlines, and there's more to look at too that for the moment I don't
recall. But it all falls apart if user has deleted a legend entry !

To some extent that scenario can be catered for if the counts don't match
by
cross-referencing colour formats, doesn't necessarily require a unique set
of colours but it might.

As a last resort can temporarily change a legend item's colour format to
something known to be unique, find it amongst the series, then reset (but
don't do that if any points have been individually formatted or they'll be
lost). Not nice!

Regards,
Peter T

"Andy Pope" wrote in message
...
Hi,

I don't think the value being returned is the true series index.
It is a value dependent upon primary/secondary axis as you point out but
it is also dependent on chart type.

Create a simple clustered ccolumn chart with 3 series.
Using the chartevents and selecting the legend entries in turn will give
you arg1 values of 1,2 and 3. Which is what you would expect.

Now change the 2nd series to a line chart and the 3rd series to a
xy-scatter chart. Do not move any to the secondary axis just yet.
Again select the 3 legend entries. This time arg1 return 1 for all three
series.

So it would appear that the limited information returned is of no use
once a chart contains series that are on the secondary axis or is a
combination chart.

Cheers
Andy

Mike wrote:
Hi there, using XL2K

I am using GetChartElement on a chart to find out which series the
user
has
clicked on in the legend, and I have run into a small (hopefully)
stumbling
block - my chart is a clustered column chart with 2 y-axes, where
series
1
and 2 are on the primary and series 3 is on the secondary.

When GetChartElement returns arg1 as the seriesindex (arg2 is presumed
empty
because that's what is supposed to happen when the elementid is
xlLegendKey
or xlLegendEntry, correct?), everything is okay if the series picked
is
one
of the two on the primary axis, but if it's series 3, then arg1 is set
to
"1", not "3" as it should be, I guess because it's series 1 on the
secondary
axis.

So the question that comes up, is that since Excel seems to be saying
"you've picked series 1 on the secondary axis", how do I find out
whether
the user's picked series 1 on the secondary axis or the primary???


----------------------------------
Please reply to NG.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I don't follow the chain of things but if user has selected a Legend item if
user or you go on to apply a format same will be adopted by the related
Series.

I've got "all locked up" by eating all the events thrown at the charts.

Hmm!

Regards,
Peter T

"Mike" wrote in message
...
Thanks for the info Peter & Andy.

Kind of funny Peter that you mention using colour to cross reference, as
the
reason I'm using this is to allow the user to change the colour of the
series on charts that I've got "all locked up" by eating all the events
thrown at the charts.

However, it sounds like I'm better off implementing this function through
a
different means.... thanks.

"Peter T" <peter_t@discussions wrote in message
...
Just to add, in theory a legend item can be related to a series with a
lot
of work from both the ChartGroups collection and Axes, don't forget
Trendlines, and there's more to look at too that for the moment I don't
recall. But it all falls apart if user has deleted a legend entry !

To some extent that scenario can be catered for if the counts don't
match
by
cross-referencing colour formats, doesn't necessarily require a unique
set
of colours but it might.

As a last resort can temporarily change a legend item's colour format to
something known to be unique, find it amongst the series, then reset
(but
don't do that if any points have been individually formatted or they'll
be
lost). Not nice!

Regards,
Peter T

"Andy Pope" wrote in message
...
Hi,

I don't think the value being returned is the true series index.
It is a value dependent upon primary/secondary axis as you point out
but
it is also dependent on chart type.

Create a simple clustered ccolumn chart with 3 series.
Using the chartevents and selecting the legend entries in turn will
give
you arg1 values of 1,2 and 3. Which is what you would expect.

Now change the 2nd series to a line chart and the 3rd series to a
xy-scatter chart. Do not move any to the secondary axis just yet.
Again select the 3 legend entries. This time arg1 return 1 for all
three
series.

So it would appear that the limited information returned is of no use
once a chart contains series that are on the secondary axis or is a
combination chart.

Cheers
Andy

Mike wrote:
Hi there, using XL2K

I am using GetChartElement on a chart to find out which series the
user
has
clicked on in the legend, and I have run into a small (hopefully)
stumbling
block - my chart is a clustered column chart with 2 y-axes, where
series
1
and 2 are on the primary and series 3 is on the secondary.

When GetChartElement returns arg1 as the seriesindex (arg2 is
presumed
empty
because that's what is supposed to happen when the elementid is
xlLegendKey
or xlLegendEntry, correct?), everything is okay if the series picked
is
one
of the two on the primary axis, but if it's series 3, then arg1 is
set
to
"1", not "3" as it should be, I guess because it's series 1 on the
secondary
axis.

So the question that comes up, is that since Excel seems to be
saying
"you've picked series 1 on the secondary axis", how do I find out
whether
the user's picked series 1 on the secondary axis or the primary???


----------------------------------
Please reply to NG.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info