Results 1 to 2 of 2

Thread: Remove a particular legend from a Chart using VBA

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10

    Lightbulb 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.
    Last edited by Admin; 01-14-2016 at 03:25 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Junior Member
    Join Date
    Dec 2018
    Posts
    1
    Rep Power
    0
    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

Similar Threads

  1. Replies: 2
    Last Post: 01-13-2014, 07:13 PM
  2. Remove Special Characters From Text Or Remove Numbers From Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 05-31-2013, 04:43 PM
  3. LTRIM() Function VBA: To Remove Spaces
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  4. Delete Remove Rows By Criteria VBA Excel
    By marreco in forum Excel Help
    Replies: 5
    Last Post: 12-20-2012, 05:56 PM
  5. Remove Unused Custom Styles VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 08-23-2012, 02:32 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •