Programming Excel with VBA and .NET

Name

datalabels.NumberFormat [= setting]

Synopsis

Sets or returns the format string used to display the data label caption. You can see the available format string settings from the Format Data Labels dialog box (Figure 17-5).

Figure 17-5. Right-click the label and choose Format Data Labels to see this dialog box

The following code sets the scale of the value axis to thousands and then formats the data labels appropriately:

Sub SetDataLabelNumberFormat( )
    Dim chrt As Chart, sr As Series, ds As DataLabels, _
      ax As Axis
    Set chrt = ActiveChart
    ' Get the value axis.
    Set ax = chrt.Axes(xlValue, xlPrimary)
    ' Scale numbers by 1000.
    ax.DisplayUnit = xlThousands
    ' Get the first series.
    Set sr = chrt.SeriesCollection(1)
    ' Make sure data labels exist.
    sr.HasDataLabels = True
    ' Get the DataLabels collection.
    Set ds = sr.DataLabels
    ' Set the number format (ex. $150K).
    ds.NumberFormat = "$#,###K"
End Sub