Results 1 to 2 of 2

Thread: Remove a particular legend from a Chart using VBA

Threaded View

Previous Post Previous Post   Next Post Next Post
  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)

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
  •