Figure A data label in edit mode

3

?dit this la be

1

Once in edit mode, we can change the text of a data label (which breaks any links) or set a new link to a worksheet cell. For instance, the code:

ActiveChart.SeriesCollection(1).DataLabels(2).Text = "=MyChartSheet!R12C2"

sets the data label for the second data point to the value of cell B12. Note that the formula must be in R1C1 notation. Unfortunately, however, Excel does not provide a way to associate all of the data labels for a data series with a worksheet range in a simple way (beyond doing this one data label at a time). So let us write a utility for this purpose and add it to SRXUtils.

When the utility is invoked, it presents a dialog (see Figure 21-34) with a list of all the data series for the selected chart. The user can select a data series and then define a range to which the data labels will be linked or from which the values will be copied. If the cell values are copied, no link is established and so changes made to the range are not reflected in the chart. There is also an option with regard to whether formatting is linked or copied.

Figure 21-34. Set Data Labels dialog

1tfn 1

'ei Labels

Copy Fcrmstting Cancel

We begin by augmenting the DataSheet sheet by adding a row for the new utility, as in Figure 2135 (the new utility is listed in row 2).

0 0

Post a comment