Programming Excel with VBA and .NET

Format Titles and Labels

Chapter 16 covered how to add titles and labels to charts, axes, and series using the AxisTitle, ChartTitle, DataLabel, and DisplayUnitLabel objects. This chapter provides a reference for those objects and shows you how to use their members to control the font, color, and orientation of those titles and labels.

All four of those objects provide similar members, but the DataLabel object is a little different since you can format the labels for the entire series through the DataLabels collection or format the label for a single point in the series through the DataLabel object.

Formatting these objects in code involves several steps:

1. Navigate to the parent object (Chart, Axis, or Series) in code.

2. Use the HasTitle, HasDisplayUnitLabel, or HasDataLabel property to make sure the object exists. You can either set this property to True to create the object or use it as part of an If statement to conditionally format the object if it exists.

3. Use the Font, Fill, Orientation, or other property to format the title or label.

The following code illustrates these steps to set the font size and style for each of the titles and labels that can appear on a chart:

Sub FormatTitlesAndLabels( ) Dim chrt As Chart, f As Font, ax As Axis, sr As Series Set chrt = ActiveChart '''''''''''''''''''''''''''''''''' ' Format ChartTitle ' Make sure chart has title chrt.HasTitle = True ' Get the font for the chart title. Set f = chrt.ChartTitle.Font ' Set the size/style f.Size = 14 f.Bold ...