Pages

Saturday, February 2, 2013

Hide all data label less than any percentage in Pie Chart Using VBA

If you want to hide all the data labels in a pie chart which are less than any specific percentage. Try this macro it will hide all data labels in chart which are less than 10%




Example 1  - Hide all data labels less than 10%

Sub hide_data_labels_1()
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 = xlDataLabelsShowPercent
            .Position = xlLabelPositionBestFit
            .Font.Bold = True
            .Font.Italic = True
            .Font.Size = 8
            .Font.Color = RGB(0, 0, 0)
            .Orientation = xlHorizontal
        End With
         'hide data lables <10 font="">
         If CInt(Left(.DataLabel.Text, Len(.DataLabel.Text) - 1)) < 10 Then .HasDataLabel = False
    End With
Next

End Sub

Example 2  - Hide all data labels less than 10%. When you have added multiple option in data label like Category Name ,Percentage , etc.


Sub hide_data_labels_2()
Dim cht As Chart
Dim i As Long
Dim srs As Series
Dim lbl As String
Set cht = Sheets("Sheet1").ChartObjects("Chart 2").Chart
Set srs = cht.SeriesCollection(1)

For i = 1 To srs.Points.Count
    With srs.Points(i)
        .HasDataLabel = True
        With .DataLabel
            .Type = xlDataLabelsShowLabelAndPercent
            .Separator = ";"
            .Position = xlLabelPositionBestFit
            .Font.Bold = True
            .Font.Italic = True
            .Font.Size = 8
            .Font.Color = RGB(0, 0, 0)
            .Orientation = xlHorizontal
        End With
        'hide data lables <10 font="">
        lbl = .DataLabel.Text
        'we are using Instrev because percentage is always displayed at the last
        lbl = Right(lbl, Len(lbl) - InStrRev(lbl, ";")) ' pass seperator
        If CInt(Left(lbl, Len(lbl) - 1)) < 10 Then .HasDataLabel = False
    End With
Next

End Sub



No comments:

Post a Comment