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

  • Thread starter Thread starter SamW
  • Start date Start date
S

SamW

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?
 
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
 
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.
 
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
 
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
 
Back
Top