Remove a particular legend from a Chart using VBA

[Admin]

I was looking for a way to remove a particular legend from a chart using VBA, but could not find. :(

Finally I explored myself and come up with this. Hope you find this useful in your dashboards :)

So this is a parametrized procedure so that one can call this by passing the necessary arguments.


Option Explicit

Sub DeleteLegendEntry(ByRef Chart_Object As ChartObject, ByVal SeriesName As String)

Dim chtChart As Chart
Dim srsSeries As Series
Dim lngColor As Long
Dim lngSrsColor As Long
Dim lngLoop As Long

Set chtChart = Chart_Object.Chart

lngColor = chtChart.PlotArea.Format.Fill.ForeColor.RGB

For Each srsSeries In chtChart.SeriesCollection
If LCase(srsSeries.Name) = LCase(SeriesName) Then
lngSrsColor = srsSeries.Format.Fill.ForeColor.RGB
srsSeries.Format.Fill.ForeColor.RGB = lngColor
Exit For
End If
Next

For lngLoop = 1 To chtChart.Legend.LegendEntries.Count
If chtChart.Legend.LegendEntries(lngLoop).LegendKey.F ormat.Fill.ForeColor.RGB = lngColor Then
chtChart.SeriesCollection(SeriesName).Format.Fill. ForeColor.RGB = lngSrsColor
chtChart.Legend.LegendEntries(lngLoop).Select
'chtChart.Legend.LegendEntries(lngLoop).Delete
Exit For
End If
Next

End Sub

Sub kTest()

DeleteLegendEntry Worksheets(1).ChartObjects(1), "Series Name"

End Sub

Note: I tested this only in Excel 2007 version.

[Kindred Spirit]

Hi thanks a lot for this. Excel does not maintain the same ordering for series and legend, if the chart has got a mixed type of series e.g. line, column etc and also primary and secondary vertical axis. So, using the color property to delete the legend entry of a particular series is a very good idea as long as colors do not repeat. However, I found the ForeColor property was not working (using Excel 2013). So, I modified the code in order to use Border color instead and it works.



Option Explicit

Function delete_legend_entry(ByRef chartObject As chartObject, ByVal seriesName As String)

' Got the idea from http://www.excelfox.com/forum/showthread.php/2075-Remove-a-particular-legend-from-a-Chart-using-VBA
' But the reference used Interior colour which is not unique for all series, so I used border colour

Dim chtChart As chart
Dim srsSeries As Series
Dim lngSrsColor As Long
Dim lngLoop As Long

Set chtChart = chartObject.chart

lngColor = chtChart.PlotArea.Format.Fill.ForeColor.RGB

For Each srsSeries In chtChart.SeriesCollection
If LCase(srsSeries.Name) = LCase(seriesName) Then
lngSrsColor = srsSeries.Border.Color
Exit For
End If
Next

For lngLoop = chtChart.Legend.LegendEntries.Count To 1 Step -1
If chtChart.Legend.LegendEntries(lngLoop).LegendKey.B order.Color = lngSrsColor Then
chtChart.Legend.LegendEntries(lngLoop).Select
chtChart.Legend.LegendEntries(lngLoop).Delete
Exit For
End If
Next

End Function


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg)
https://eileenslounge.com/viewtopic.php?p=316705#p316705 (https://eileenslounge.com/viewtopic.php?p=316705#p316705)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597 (https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (https://www.eileenslounge.com/viewtopic.php?p=314940#p314940)
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926 (https://www.eileenslounge.com/viewtopic.php?p=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (https://www.eileenslounge.com/viewtopic.php?p=314920#p314920)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836 (https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)