Pages

Saturday, January 26, 2013

Add Custom DataLabels in Chart

If you want to add your own datalabels to series in the chart .Try this macro





Sub custom_chart_labels()
Dim cht As Chart
Dim i As Long
Dim srs As Series
Dim lbl As String

'change the chart name here
Set cht = Sheets("Sheet1").ChartObjects("Chart 1").Chart
Set srs = cht.SeriesCollection(1)

For i = 1 To srs.Points.Count
' change the llokup column if required
lbl = Sheets("Sheet1").Range("c" & Application.WorksheetFunction.Match(srs.XValues(i), Sheets("Sheet1").Range("a:a"), 0))
    With srs.Points(i)
        .HasDataLabel = True
        ' add custom data labels
        With .DataLabel
            .Text = lbl
            .Position = xlLabelPositionOutsideEnd
            .Font.Bold = True
            .Font.Italic = True
            .Font.Size = 8
            .Font.Color = RGB(255, 255, 255)
            .Orientation = xlHorizontal
            .Format.Fill.BackColor.RGB = RGB(255, 0, 0)
            .Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End With
    End With
    
Next

End Sub


No comments:

Post a Comment