Remove a particular legend from a Chart using VBA

**Admin** · 01-14-2016, 03:18 PM

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.

**Kindred Spirit** · 12-20-2018, 06:17 PM

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