A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I find the series associated with a given legend entry.



 
 
Thread Tools Display Modes
  #1  
Old August 17th, 2009, 12:32 PM posted to microsoft.public.excel.charting
SamW
external usenet poster
 
Posts: 12
Default How do I find the series associated with a given legend entry.


I have a problem, whereby I need to find the association between a legend
entry and a chart series (in order that I can do something with the legend
entry for a specific series if it exists).

I cannot find a way to establish a firm link between a series and its
legend, for instance:

The chart has 5 series.
The legend had 5 series but the user (or something/somebody) deleted 2 of
these legend entries so there are 3 legend entries.

So, at this point lets say I have code which has a specific series in its
hand:
Excel.Series series = _chart.Series(3);

And with this series I want to delete the legend entry:
Excel.LegendEntry entry = _chart.Legend.LegendEntries( entryForSeries3 )

Is there something I am missing here? Or is there a method on an object that
I haven't seen that allows me to get the LegendEntry associated with a series?
  #2  
Old August 17th, 2009, 03:50 PM posted to microsoft.public.excel.charting
Luke M
external usenet poster
 
Posts: 2,672
Default How do I find the series associated with a given legend entry.

We can hide a specific series by controlling the PlotOrder. If we make the
target series plotOrder = 1, then we can delete legend entry 1. Then, restore
plot order to what it was before. Note that this macro assumes chart is
already activated, but I presume you can figure out how to code that part.

Sadly, it also makes the rather large assumption that the legend entry has
not already been deleted. But hopefully this gives you a start.

Sub HideName()
Dim NSrs As Integer
Dim CurrentOrder As Integer

'Name of series to hide legend for
xName = "MySeries"

With ActiveChart
NSrs = .SeriesCollection.Count
For i = 1 To NSrs
If .SeriesCollection(i).Name = "Girl" Then
CurrentOrder = .SeriesCollection(i).PlotOrder
.SeriesCollection(i).PlotOrder = 1
.Legend.LegendEntries(1).Delete
.SeriesCollection(i).PlotOrder = CurrentOrder
End If
Next
End With
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SamW" wrote:


I have a problem, whereby I need to find the association between a legend
entry and a chart series (in order that I can do something with the legend
entry for a specific series if it exists).

I cannot find a way to establish a firm link between a series and its
legend, for instance:

The chart has 5 series.
The legend had 5 series but the user (or something/somebody) deleted 2 of
these legend entries so there are 3 legend entries.

So, at this point lets say I have code which has a specific series in its
hand:
Excel.Series series = _chart.Series(3);

And with this series I want to delete the legend entry:
Excel.LegendEntry entry = _chart.Legend.LegendEntries( entryForSeries3 )

Is there something I am missing here? Or is there a method on an object that
I haven't seen that allows me to get the LegendEntry associated with a series?

  #3  
Old August 18th, 2009, 01:15 PM posted to microsoft.public.excel.charting
John Mansfield
external usenet poster
 
Posts: 218
Default How do I find the series associated with a given legend entry.

If you delete the current legend and then add a new one, the new legend
should show all five series. At that point you could link the series and
legend by setting up a variable that you would use for both you series and
legend entries i.e. something like this:

Sub Test()

Dim Cht As Chart
Dim SrsNbr As Integer

Set Cht = ActiveChart

SrsNbr = 3

Cht.SeriesCollection(SrsNbr).Select
Cht.Legend.LegendEntries(SrsNbr).Select

End Sub

Unfortunately this will only work if all legend entries are included (none
have been deleted). There's doesn't seem to be an object or method allowing
the functionality you need that I can find. So, if a legend entry is
deleted you would have to somehow keep track of the new order in your code.

--
John Mansfield
http://www.cellmatrix.net


"SamW" wrote:


I have a problem, whereby I need to find the association between a legend
entry and a chart series (in order that I can do something with the legend
entry for a specific series if it exists).

I cannot find a way to establish a firm link between a series and its
legend, for instance:

The chart has 5 series.
The legend had 5 series but the user (or something/somebody) deleted 2 of
these legend entries so there are 3 legend entries.

So, at this point lets say I have code which has a specific series in its
hand:
Excel.Series series = _chart.Series(3);

And with this series I want to delete the legend entry:
Excel.LegendEntry entry = _chart.Legend.LegendEntries( entryForSeries3 )

Is there something I am missing here? Or is there a method on an object that
I haven't seen that allows me to get the LegendEntry associated with a series?

  #4  
Old August 18th, 2009, 03:49 PM posted to microsoft.public.excel.charting
SamW
external usenet poster
 
Posts: 12
Default How do I find the series associated with a given legend entry.

In summary I guess that both of your post's answered the question, there just
isn't a sure-fire way to work out what legend entry ties to what series.

I came up with a fairly horrible other way which meets my needs (but it
certainly horrific).

In essence what I ended up doing is using the
LegendKey.LegendEntry.Format.Fill.BackColor and matching that with the
Series.Format.Fill.BackColor.

There's a few different ways that this approach could go wrong, its not
advisable (for instance not all series have fill colors and in those cases
you would have to use the Marker colors or something instead for lines), it
happened to work in my case.

Some kind of more complex matching method between the LegendKey and the
series would be possible given time if anybody else gets stuck with this.

Thanks
Sam

"SamW" wrote:


I have a problem, whereby I need to find the association between a legend
entry and a chart series (in order that I can do something with the legend
entry for a specific series if it exists).

I cannot find a way to establish a firm link between a series and its
legend, for instance:

The chart has 5 series.
The legend had 5 series but the user (or something/somebody) deleted 2 of
these legend entries so there are 3 legend entries.

So, at this point lets say I have code which has a specific series in its
hand:
Excel.Series series = _chart.Series(3);

And with this series I want to delete the legend entry:
Excel.LegendEntry entry = _chart.Legend.LegendEntries( entryForSeries3 )

Is there something I am missing here? Or is there a method on an object that
I haven't seen that allows me to get the LegendEntry associated with a series?

  #5  
Old August 18th, 2009, 06:28 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default How do I find the series associated with a given legend entry.

You're right, there's no reliable way in the object model to match up
series with legend entries.

But you could extend your approach further. You might have multiple
series with the same formatting, which complicates things, or you might
have points in the series which have unique formatting, which
complicates things more.

I won't write the code, but I'll outline for you how I would do this.


Function WhichLegendEntry(WhichSeries) as long

WhichLegendEntry=0 ' default value, returned if no match

get formatting of series WhichSeries
if you suspect points are differently formatted, get series formats,
then make an array for the points, and if a point's formatting
differs from the series formatting, store it in the array

Apply some far out format to the series

find the legend entry that has the same wild format
Return this from the function
Return zero if no match

Reapply original series formatting
check array for points with unique formatting,
reapply as applicable.

End Function


- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



SamW wrote:
In summary I guess that both of your post's answered the question, there just
isn't a sure-fire way to work out what legend entry ties to what series.

I came up with a fairly horrible other way which meets my needs (but it
certainly horrific).

In essence what I ended up doing is using the
LegendKey.LegendEntry.Format.Fill.BackColor and matching that with the
Series.Format.Fill.BackColor.

There's a few different ways that this approach could go wrong, its not
advisable (for instance not all series have fill colors and in those cases
you would have to use the Marker colors or something instead for lines), it
happened to work in my case.

Some kind of more complex matching method between the LegendKey and the
series would be possible given time if anybody else gets stuck with this.

Thanks
Sam

"SamW" wrote:

I have a problem, whereby I need to find the association between a legend
entry and a chart series (in order that I can do something with the legend
entry for a specific series if it exists).

I cannot find a way to establish a firm link between a series and its
legend, for instance:

The chart has 5 series.
The legend had 5 series but the user (or something/somebody) deleted 2 of
these legend entries so there are 3 legend entries.

So, at this point lets say I have code which has a specific series in its
hand:
Excel.Series series = _chart.Series(3);

And with this series I want to delete the legend entry:
Excel.LegendEntry entry = _chart.Legend.LegendEntries( entryForSeries3 )

Is there something I am missing here? Or is there a method on an object that
I haven't seen that allows me to get the LegendEntry associated with a series?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.