Using VBA to Display Arbitrary Data Labels on a Chart

One of the most frequent complaints about Excel's charting is its inflexible data labeling feature. For example, consider the XY chart in Figure 18-11. It would be useful to display the associated name for each data point. However, you can search all day, and you'll never find the Excel command that lets you do this automatically. Such a command doesn't exist. Data labels are limited to the data values only - unless you want to edit each data label manually and replace it with text (or a formula) of your choice.

Figure 18-11: An XY chart with no data labels.

The DataLabelsFromRange procedure works with the first chart on the active sheet. It prompts the user for a range and then loops through the Points collection and changes the Text property to the values found in the range.

 Sub DataLabelsFromRange()     Dim DLRange As Range     Dim Cht As Chart     Dim i As Integer, Pts As Integer '   Specify chart     Set Cht = ActiveSheet.ChartObjects(1).Chart '   Prompt for a range     On Error Resume Next     Set DLRange = Application.InputBox _       (prompt:="Range for data labels?", Type:=8)     If DLRange Is Nothing Then Exit Sub     On Error GoTo 0 '   Add data labels     Cht.SeriesCollection(1).ApplyDataLabels _       Type:=xlDataLabelsShowValue, _       AutoText:=True, _       LegendKey:=False '   Loop through the Points, and set the data labels     Pts = Cht.SeriesCollection(1).Points.Count     For i = 1 To Pts         Cht.SeriesCollection(1). _           Points(i).DataLabel.Text = DLRange(i)     Next i End Sub 

CD-ROM  

This example, named  data labels.xlsm , is available on the companion CD-ROM.

Figure 18-12 shows the chart after running the DataLabelsFromRange procedure and specifying A2:A9 as the data range.

Figure 18-12: This XY chart has data labels, thanks to a VBA procedure.

A data label in a chart can also consist of a link to a cell . To modify the DataLabelsFromRange procedure so it creates cell links, just change the statement within the For-Next loop to:

 Cht.SeriesCollection(1).Points(i).DataLabel.Text = _             "=" & "'" & DLRange.Parent.Name & "'!" & _             DLRange(i).Address(ReferenceStyle:=xlR1C1) 

Note  

The preceding procedure is rather crude and does very little error checking. In addition, it only works with the first Series object. The Power Utility Pak add-in (which you can obtain by using the coupon in the back of the book) includes a much more sophisticated chart data “labeling utility.