Saturday, February 2, 2013

Hide - Unhide Legend Entry Using VBA

If you want to hide , add or delete legend entries in the chart using VBA. Try the code given below -




Hide Legend Entry Using VBA


Sub hide_legend()
Dim cht As Chart
Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    With cht
        .HasLegend = False
    End With
End Sub

ADD Legend Entry Using VBA


Sub add_legend()
Dim cht As Chart
Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    With cht
        .HasLegend = True
        .Legend.Font.Size = 8
        .Legend.Font.Name = "Arial"
        .Legend.Font.Bold = True
        .Legend.Font.Color = RGB(0, 0, 0)
        .Legend.Font.Italic = True
        .Legend.Position = xlLegendPositionBottom
    End With
End Sub

Delete Legend Entries on the basis of series name Using VBA


Sub delete_legend_entry()

Dim cht As Chart
Dim i As Long

Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    'loop in all series name
    For i = 1 To cht.SeriesCollection.Count
        'Match the series name
        If cht.SeriesCollection(i).Name = "Jan" Or cht.SeriesCollection(i).Name = "Feb" Then
            'delete the legend entry
            cht.Legend.LegendEntries(i).Delete
        End If
    Next

End Sub




1 comment:

  1. With integrity, you have nothing to fear, since you have nothing to hide. With integrity, you will do the right thing, so you will have no guilt. See the link below for more info.


    #hide
    www.ufgop.org

    ReplyDelete

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...