Legend entry indexing for stacked column chart in Excel 2010

[AlisonS]

Some years ago I wrote an Excel routine to add totals to a stacked column chart which adds a zero-value series to the chart and then deletes the legend entry for the new series using, in effect, ActiveChart.Legend.LegendEntries(ActiveChart.SeriesCollection.Count).Delete.

I am now updating this for Excel 2010 and am having a problem because Microsoft have changed the way the legend entries are indexed if the legend is to the right (or left) of the chart: in this situation the above code now deletes the legend entry relating to the first series rather than the last! If, however, the legend is at the bottom of the chart, the above code works fine as the legend entries are indexed in the opposite, and usual, order (ie, LegendEntries(1) relates to SeriesCollection(1) etc).

If the legend is in its automatic position, then I can read this and react accordingly, but unfortunately my users cannot be relied upon to leave things alone, so I was hoping to find a property to at least tell me the orientation of the legend, regardless of where it actually is, but I can't find one.

I have read Jeffrey Smith's thread on Removing Legend Entries with VBA but, unfortunately, his code also deletes the wrong legend entry in the above scenario.

Any suggestions as to how I can get round this will be much appreciated.

(I first posted this question to answers.microsoft.com, but have had no responses as yet.)

 

[moonfish]

The aria-posinset property should be usable on a legend. That might be a solution for you:
http://msdn.microsoft.com/en-us/library/cc304087(v=VS.85).aspx

If that doesn't work, here's a list of things that can be used on a legend:
http://msdn.microsoft.com/en-us/library/cc304087(v=VS.85).aspx

 

[AlisonS]

Thanks for this, but I don't believe that's accessible from VBA, unless I'm missing something?

 

[moonfish]

v = Library.getAttribute("aria-posinset");

The way to use these in VBA can be found under Scripting on each of the pages.

 

[AlisonS]

I did actually try the code, replacing 'object' with ActiveChart.Legend as that is the object I want the property for, but I got the error 'Object doesn't support this property or method' and I can't find a relevant object library to reference.

I've now tried your version but 'library' is not a vba object or keyword.

Having read what limited information I can find online about this property it sounds like it would just give me the legend's position, anyway, not it's orientation.

If you still think this property will provide me with the information I need, could you please give me more details about how to use it.

Thanks.

 

[moonfish]

Library was actually a typo, sorry.

What about align? http://msdn.microsoft.com/en-us/library/ms533067(v=VS.85).aspx

 

[AlisonS]

I do not know how to make use of this from vba. Please could you explain.

Thank you.

 

[Jon Peltier]

I don't know why moonfish thought that the "ARIA" (Accessible Rich Internet Applications) framework would be relevant to an Excel chart.

I've encountered the same inconsistency in Excel 2007 charts with my own work. I tried the following in 2007, not in 2010, but most things about charting that are unexpected in 2010 are also unexpected in 2007.

If the legend is in one of its built-in positions (top, left, bottom, right, top right corner), the legend entry ordering is consistent if not logical. The position is the chart.legend.position property.

In top and bottom position, the legend entries are arranged in rows, and numbering goes from left to right, the same as the order you added them to the chart. To delete the last one you added, you delete the one with the highest index as you are accustomed to doing.

In the left, right, and top right corner positions, the legend entries are arranged in columns, and are numbered top to bottom, in reverse order of how you added them. To delete that last one you added, delete legend entry 1.

So far so good. Now for the bad news: in the custom position (i.e., the user has dragged the legend somewhere and possibly resized it to change its orientation), the order of legend entries is the same as entries in the most recent built-in position the legend was in. So depending on how many times your users changed the position of the legend before dragging it around, you will not have any idea what order the legend entries are in.

All is not lost. You can do a select case on the legend position, if it's built-in, you know the order. If it's custom, get the legend's top and left properties, move the legend to a built-in position, delete the known entry, then move the legend back to its custom left and top position. You'll probably have to get the left, top, width, and height of the plot area before moving the legend, and set them back when the legend is returned.

 

[AlisonS]

Thank you for a reply that actually makes sense to me!

I don't know why moonfish thought that the "ARIA" (Accessible Rich Internet Applications) framework would be relevant to an Excel chart.

I wondered the same thing, but I assumed that moonfish knew something I didn't. Spent quite a bit of time looking into it...

All is not lost. You can do a select case on the legend position, if it's built-in, you know the order. If it's custom, get the legend's top and left properties...

Yes, that's the conclusion I'd come to as well, failing any other suggestions. So, thanks for the confirmation - at least I can stop looking now.

Alison