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