Dynamic Chart - Hide legend entry for unplotted series

[Theangelp]

Hi All,

I have a dynamic chart, allowing me to select which series I wish to show. My problem is that, for any series I don't want shown, the series colour is still showing on the legend. I know I can select the colour on the legend and simply delete but is there some way (VBA?) that I can get this to happen automatically (by basing the colour on a cell value maybe?)?


Thanks in advance.

[MrShorty]

I know I can select the colour on the legend and simply delete but is there some way (VBA?) that I can get this to happen automatically

Just about anything you can do manually in Excel can be done using VBA and automated. What is your skill level with VBA or other programming languages? How familiar are you with Excel's object model?

The real difficulty, I think, with automating something like is not in deleting the legend entries, but in restoring the deleted legend entries. Microsoft did not provide a simple way to restore a legend entry once deleted. You essentially are forced to delete the legend entirely and then recreate the legend. So the first step in the procedure will be to remove the legend and recreate it. This is accomplished using the .haslegend property of the chart object. https://msdn.microsoft.com/en-us/lib.../ff840365.aspx

Once you have the legend with all of the legendentries in place, then you need to loop through the seriescollection and identify which legendentries to delete. Once those are identified, then you can loop through the legendentries collection and delete the desired legendentries with the legendentry.delete method. The help file above has a menu on the left hand side that allows you to navigate through Excel's object model to learn the object model and what you can do with each object.

Is a legend absolutely necessary? An easier approach might be to get rid of the legend entirely and use a data label on one of the points (first, last, high, or low depending on what will be most effective) for each series. https://support.office.com/en-us/art...9-e8109fdc1613
https://support.office.com/en-us/art...rs=en-US&ad=US

The main advantage that I see to using data labels instead of legend entries is that it will be much easier to maintain -- it may even completely eliminate the need for further maintenance. Unlike legend entries, data labels are only present when the labeled point is present. So, when a series is empty, the data label will not display. However, once data is entered into that series, the data labels will automatically reappear. Once set up, there would be no need for a VBA routine (or other method) to show/delete legend entries.