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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|