Pages

Saturday, February 2, 2013

Hide and Un-hide Data Lables Using VBA

If you want to add data labels to Chart using VBA. Try this macro -



To view Data Labels


Sub show_data_labels()
Dim cht As Chart
Dim i As Long
Dim srs As Series

Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
Set srs = cht.SeriesCollection(1)

For i = 1 To srs.Points.Count
    With srs.Points(i)
        .HasDataLabel = True
        With .DataLabel
            .Type = xlDataLabelsShowValue
            .Position = xlLabelPositionOutsideEnd
            .Font.Bold = True
            .Font.Italic = True
            .Font.Size = 8
            .Font.Color = RGB(0, 0, 0)
            .Orientation = xlHorizontal
        End With
    End With
Next

End Sub


To Hide Data Labels


Sub hide_data_labels()
    Dim cht As Chart
    Dim i As Long
    Dim srs As Series
    
    Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    Set srs = cht.SeriesCollection(1)
    
    For i = 1 To srs.Points.Count
        srs.Points(i).HasDataLabel = False
    Next

End Sub


Download Working File

No comments:

Post a Comment