Setting a Charts Data Point Labels

As you may know, data labels can be edited individually by clicking twice (pausing in between clicks) on a data label. This places the label in edit mode, as shown in Figure 1-4. 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. Accomplishing the same thing programmatically is also very easy. 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. (We will explain the code in Chapter 21, so don't worry about the details now.)

Figure 1-4. A data label in edit mode

Figure 1-4. A data label in edit mode

Unfortunately, however, Excel does not provide a simple way to link all of the data labels for a data series with a worksheet range, beyond doing this one data label at a time. In Chapter 21, we will create such a utility, the dialog for which is shown in Figure 1-5. This dialog provides 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 to control whether formatting is linked or copied.

Figure 1-5. Set Data Labels dialog

Figure 1-5. Set Data Labels dialog

Negative Value Bubl Chart Excel

I hope that these illustrations have convinced you that Excel programming can at times be very useful. Of course, you can do much more mundane things with Excel programs, such as automating the printing of charts, sorting worksheets alphabetically, and so on.

0 0

Post a comment