Remove a particular legend from a Chart using VBA

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.

Code:

---

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.Format.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.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
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=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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.

Code:

---

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.Border.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/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
https://eileenslounge.com/viewtopic.php?p=316705#p316705
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?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
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=314920#p314920
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=27&t=40621&p=314621#p314621
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA