Example: Setting Data Series 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 21-33.

A data label in edit mode

Figure 21-33. A data label in edit mode

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.

Set Data Labels dialog

Figure 21-34. Set Data Labels dialog

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

DataSheet

Figure 21-35. DataSheet

Next, create the dialog shown in Figure 21-34. We have used the RefEdit control, which simulates the Excel feature of choosing a range using the mouse. Unfortunately, this control is not documented in the help files. (You can get a list of its properties, methods, and events in the Microsoft Object Browser, but no help.) In any case, we are interested in only one or two properties, as shown in the upcoming code.

The LabelDataSeries procedure, which is called when the menu item is selected, should be placed in a new standard code module called basDataLabels. The Declarations section of the code module has some module-level declarations, which are shown in Example 21-10.

Example 21-10. The Declarations Section of the basDataLabels Code Module 

Option Explicit

Private Type utDataLabels
  HasDataLabel As Boolean
  Label As String
  FontName As String
  FontSize As Single
  Color As Long
  Bold As Boolean
  Italic As Boolean
End Type

Public LabelsForUndo() As utDataLabels
Public DataSeries As Series
Public cPoints As Integer
Public bCopyFormatting As Boolean
Public oChart As Chart
Dim cSeries as Long

Note, in particular, the user-defined type declaration. This saves the original data labels for a possible Undo operation. It can hold not only the data label's text, but also the formatting options that can be set using this utility.

The LabelDataSeries procedure, which is shown in Example 21-11, first verifies that a chart sheet or embedded chart is selected. Then it sets the global variable oChart to refer to this chart. This variable will be used by the dialog, so it needs to be public. Next, it sets the global variable cSeries to the number of data series in the chart. If the chart has no data series, a message is displayed. If everything is satisfactory, the Set Data Labels dialog is displayed.

Example 21-11. The LabelDataSeries Procedure 

Public Sub LabelDataSeries()

' Verify that a chart sheet or
' an embedded chart is active.
' If so, set it to oChart.

Set oChart = Nothing

If ActiveWorkbook.Sheets.Count = 0 Then
  MsgBox "Active workbook has no charts. Exiting.", vbInformation
  Exit Sub
End If

On Error GoTo NoChartActive
Set oChart = ActiveChart
If Not oChart Is Nothing Then
  ' Check for data series
  cSeries = oChart.SeriesCollection.Count
  If cSeries = 0 Then
    MsgBox "Active chart has no data series.", vbInformation
    Exit Sub
  End If
  ' If so, display dialog
  dlgDataLabels.Show
Else
  MsgBox "This utility requires that a chart be selected.", vbInformation
End If

Exit Sub

NoChartActive:
  MsgBox "This utility requires that a chart be selected.", vbInformation
Exit Sub

End Sub

After creating basDataLabels, you should create the dialog in Figure 21-34, name it dlgDataLabels, and assign the string "Set Data Labels" to its Caption property. We have created several custom dialogs earlier in the book, so we will not go into any details here. (You can design your dialog differently if you wish. There is nothing sacred about my design.) The control names are:

CmdCancel

For the Cancel button

CmdSetLabels

For the Set Labels button

cmdUndo

For the Undo button

LblSeries

For the "Choose a Series:" label

LstSeries

For the list box

LblRange

For the "Choose a Range for the Labels" label

reditLabels

For the Reference Editor control

fraOptions

For the frame

OptLink

For the Link option button

OptCopy

For the Copy option button

chkOption

For the Copy Formatting check box

You should not have any trouble determining which name goes with which control (which is a benefit of using a naming convention, after all).

Most of the code behind the dialog is pretty straightforward. The Initialize event, shown in Example 21-12, first fills the list box (lstSeries) with a list of all of the data series in the chart oChart. Then it initializes some of the other controls.

Example 21-12. The Initialize Event Procedure 

Private Sub UserForm_Initialize()

' oChart is set to refer to the active chart
' cSeries has count of series

Dim ds As Series

' Fill the lstSeries
For Each ds In oChart.SeriesCollection
  lstSeries.AddItem ds.Name
Next

optCopy.Value = True
chkOption.Caption = "Copy Formatting"
chkOption.Accelerator = "F"
cmdUndo.Enabled = False

End Sub

We want the caption (and accelerator key) of the check box at the bottom of the dialog to change based on the choice of option button (Link or Copy). Hence, we need some code in the appropriate Click events, as shown in Example 21-13.

Example 21-13. The Option Buttons' Click Events 

Private Sub optCopy_Click()

' Set correct check box label and enable
chkOption.Caption = "Copy Formatting"
chkOption.Accelerator = "F"
chkOption.Enabled = True

End Sub


Private Sub optLink_Click()

' Set correct check box label and enable
chkOption.Caption = "Link Number Format"
chkOption.Accelerator = "N"
chkOption.Enabled = True

End Sub

As usual, the command buttons' Click events are short. They are shown in Example 21-14.

Example 21-14. The cmdCancel and cmdSetLabels Click Events 

Private Sub cmdCancel_Click()
  Unload Me
End Sub

Private Sub cmdSetLabels_Click()
  DoDataLabels
End Sub

The main portion of the code, the DoDataLabels procedure shown in Example 21-15 (and housed in the UserForm module), checks to see if a data series and label range have been selected and compares their sizes, which must match or else an error message is displayed.

Example 21-15. The DoDataLabels Procedure 

Sub DoDataLabels()

Dim i As Integer
Dim rngLabels As Range
Dim fnt As Font

' Is a data series selected? Get its size.
If lstSeries.ListIndex = -1 Then
  MsgBox "You must select a data series.", vbInformation
  Exit Sub
Else
  Set DataSeries = oChart.SeriesCollection(lstSeries.ListIndex + 1)
    ' There will be an error if the chart does not support data points
    On Error Resume Next
    cPoints = DataSeries.Points.Count
    If Err.Number <> 0 Then
      MsgBox "Charts of the selected type do not support data labels.", _
             vbCritical
      Unload Me
      Exit Sub
    End If
End If

' Get the labels range
Set rngLabels = Range(reditLabels.Value)
If rngLabels Is Nothing Then
  MsgBox "You must select a range of cells equal in number to " & _
         "the number of data points in the series.", vbInformation
  Exit Sub
End If

' Check counts
If cPoints <> rngLabels.Count Then
  MsgBox "The number of label cells (" & rngLabels.Count & _
         ") does not equal the number of data points (" & cPoints & _
         ") in the selected series.", vbInformation
  Exit Sub
End If

' Check for existing labels and save them
If DataSeries.HasDataLabels Then
  ' Dimension the array
  ReDim LabelsForUndo(1 To cPoints)
  ' Fill array
  For i = 1 To cPoints
    LabelsForUndo(i).HasDataLabel = DataSeries.Points(i).HasDataLabel
    If LabelsForUndo(i).HasDataLabel Then
      ' Save the label text
      LabelsForUndo(i).Label = DataSeries.Points(i).DataLabel.Text
      ' Save the formatting
      With DataSeries.Points(i).DataLabel.Font
        LabelsForUndo(i).FontName = .Name
        LabelsForUndo(i).FontSize = .Size
        LabelsForUndo(i).Color = .Color
        LabelsForUndo(i).Bold = .Bold
        LabelsForUndo(i).Italic = .Italic
      End With
    End If
  Next
  cmdUndo.Enabled = True
End If

' Now do data labels based on options
If optLink Then
  For i = 1 To cPoints
    DataSeries.Points(i).HasDataLabel = True
    DataSeries.Points(i).DataLabel.Text = "=" & rngLabels.Parent.Name _
      & "!" & rngLabels.Cells(i).Address(ReferenceStyle:=xlR1C1)
    If chkOption Then
      ' Set number format link
      DataSeries.Points(i).DataLabel.NumberFormatLinked = True
    End If
  Next
Else
  For i = 1 To cPoints
    DataSeries.Points(i).HasDataLabel = True
    DataSeries.Points(i).DataLabel.Text = rngLabels.Cells(i).Value
    If chkOption Then
      bCopyFormatting = True
      With DataSeries.Points(i).DataLabel.Font
        .Name = rngLabels.Cells(i).Font.Name
        .Size = rngLabels.Cells(i).Font.Size
        .Bold = rngLabels.Cells(i).Font.Bold
        .Italic = rngLabels.Cells(i).Font.Italic
        .Color = rngLabels.Cells(i).Font.Color
      End With
      DataSeries.Points(i).DataLabel.NumberFormat = _
                 rngLabels.Cells(i).NumberFormat
    Else
      bCopyFormatting = False
    End If
  Next
End If

End Sub

The Undo command button's Click event, which is shown in Example 21-16, restores the original data labels that are saved in the DoDataLabels procedure.

Example 21-16. The cmdUndo_Click Event Procedure 

Private Sub cmdUndo_Click()

' Restore labels for DataSeries

Dim i As Integer

For i = 1 To cPoints
  If LabelsForUndo(i).HasDataLabel Then
    DataSeries.Points(i).HasDataLabel = True
    DataSeries.Points(i).DataLabel.Text = LabelsForUndo(i).Label
    If bCopyFormatting Then
      ' Restore formatting
      With DataSeries.Points(i).DataLabel.Font
        .Name = LabelsForUndo(i).FontName
        .Size = LabelsForUndo(i).FontSize
        .Color = LabelsForUndo(i).Color
        .Bold = LabelsForUndo(i).Bold
        .Italic = LabelsForUndo(i).Italic
      End With
    End If
  Else
    DataSeries.Points(i).HasDataLabel = False
  End If
Next
cmdUndo.Enabled = False

End Sub