Using VBA to apply chart formatting

A common type of chart macro applies formatting to a chart. The following example applies several different types of formatting to the active chart:

Sub ChartMods1()

If ActiveChart Is Nothing Then MsgBox "Select a chart." Exit Sub End If

With ActiveChart .Type = xlArea

.ChartArea.Font.Name = "Arial" .ChartArea.Font.FontStyle = "Regular" .ChartArea.Font.Size = 9 .PlotArea.Interior.Colorlndex = xlNone .Axes(xlValue).TickLabels.Font.Bold = True .Axes(xlCategory).TickLabels.Font.Bold = True If .HasLegend = True Then .Legend.Position = xlBottom End With End Sub

The procedure starts by ensuring that a chart is selected. If not, a message appears, and the procedure ends. Also, notice the use of an If statement to determine whether the chart has a legend. This is to avoid an error that would occur when trying to set the Position property of the Legend object if the chart had no legend.

A workbook with this example is available on the companion CD-ROM.

Following is another version of the ChartMods procedure. In this case, it works on a specific chart: the one contained in a ChartObject named Chart 1, located on Sheetl. Notice that the chart is never activated.

Sub ChartMods2()

With Sheets("Sheet1").ChartObjects("Chart 1").Chart .Type = xlArea

.ChartArea.Font.Name = "Arial" .ChartArea.Font.FontStyle = "Regular" .ChartArea.Font.Size = 9 .PlotArea.Interior.Colorlndex = xlNone .Axes(xlValue).TickLabels.Font.Bold = True .Axes(xlCategory).TickLabels.Font.Bold = True If .HasLegend = True Then .Legend.Position = xlBottom End With End Sub

0 0

Post a comment