Part VI: Automating Excel
For Each Cht In ActiveWorkbook.Charts
Cht.ChartType = xlColumnClustered
Next Cht
End Sub
Modifying chart properties
The following example changes the legend font for all charts that are on the active sheet.
It uses a For-Next loop to process all ChartObject objects and sets the HasLegend
property to True. The code then adjusts the properties of the Font object contained in the
Legend object:
Sub LegendMod()
Dim ChtObj As ChartObject
For Each ChtObj In ActiveSheet.ChartObjects
ChtObj.Chart.HasLegend = True
With ChtObj.Chart.Legend.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
End With
Next ChtObj
End Sub
Applying chart formatting
This example applies several formatting types to the specified chart (in this case, Chart 1
on the active sheet):
Sub ChartMods()
With ActiveSheet.ChartObjects("Chart 1").Chart
.ChartType = xlColumnClustered
.ChartTitle.Text = "XYZ Corporation"
.ChartArea.Font.Name = "Arial"
.ChartArea.Font.FontStyle = "Regular"
.ChartArea.Font.Size = 9
.PlotArea.Interior.ColorIndex = 6
.Axes(xlValue).TickLabels.Font.Bold = True
.Axes(xlCategory).TickLabels.Font.Bold = True
End With
End Sub
The best way to learn about the objects, properties, and methods that you need to code for
charts is to record a macro while you create or apply various changes to a chart.
