Microsoft Office Tutorials and References
In Depth Information
Applying linked data labels
For Each Pt In Ser.Points
Pt.DataLabel.Text = Range(“A1”).Offset(Counter, 0)
Counter = Counter + 1
Next Pt
End Sub
Figure 16-11: Excel does not provide a direct way to use the text in
column A as data labels in the chart.
The ApplyDataLabels macro creates an object variable ( Ser ) that represents the
chart’s data series. It then sets the HasDataLabels property to True (without this
statement, the macro would end with an error). The next statement initializes a
variable ( Counter ) to 1. The next four statements comprise a For Each-Next loop,
which loops through each Point object in the series. The code sets the Text property
of the Point object’s DataLabel object equal to a cell that is offset from cell A1.
The offset row is specified by the Counter variable, which is incremented each time
through the loop.
Applying linked data labels
A data label can contain a simple formula that refers to a cell. In such a case, the
data label is linked to that cell — if the cell changes, so does the corresponding data
label. It’s a simple matter to modify this macro so that it creates links to the cells.
Only one statement needs to be changed:
Pt.DataLabel.Text = “=” & Range(“A1”).Offset(Counter, 0) _
.Address(True, True, xlR1C1, True)
Search JabSto ::

Custom Search