Excel 2010 Bible

Part VI: Programming Excel with VBA

898

Modifying the chart type

The following example changes the chart type of every embedded chart on the active sheet. It

makes each chart an area chart by adjusting the ChartType property of the Chart object. A

built-in constant, xlColumnClustered, represents a standard column chart.

Sub ChartType()

Dim ChtObj As ChartObject

For Each ChtObj In ActiveSheet.ChartObjects

ChtObj.Chart.ChartType = xlColumnClustered

Next ChtObj

End Sub

The preceding example uses a For-Next loop to cycle through all the ChartObject objects on

the active sheet. Within the loop, the chart type is assigned a new value, making it an area chart.

The following macro performs the same function but works on all chart sheets in the active

workbook:

Sub ChartType2()

Dim Cht As Chart

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 uses the HasLegend property to

ensure that the chart has a legend. The code then adjusts the properties of the Font object con-

tained 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