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,123
    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.

    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
    Last edited by DocAElstein; 05-20-2024 at 03:36 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
    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
    Last edited by DocAElstein; 04-24-2024 at 07:40 PM.

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
  •