Hi,

i have some 20 charts in a sheet along with the data. I want to change
the fonts of all the charts i.e. title, axis etc. i was able to do
this but some of my charts also have secondary axis and font for the
secondary is not changing.

How to achieve this. I used below code to achieve. Please tell where i
am wrong.

Sub Test()
Dim sht As Worksheet
Dim chtObject As ChartObject
Dim cht As Chart



For Each sht In Worksheets
For Each chtObject In sht.ChartObjects
Set cht = chtObject.Chart
''With cht.Axes(xlCategory)
' .TickLabels.Font.Size = 8
' .AxisTitle.Font.Size = 8
' End With
'On Error Resume Next
For Each a In cht.Axes
With cht.Axes(xlValue)
.TickLabels.Font.Size = 8
.AxisTitle.Font.Size = 8
End With

Next a
With cht.DataTable
.Font.Size = 8
End With
With cht.ChartTitle
.Font.Size = 8
End With
Next chtObject
End Sub

Thanks for the help in advance.

Navin

Hi Navin,

This bit -

For Each a In cht.Axes
With cht.Axes(xlValue)
.TickLabels.Font.Size = 8
.AxisTitle.Font.Size = 8
End With

try -
For Each a In cht.Axes
a.TickLabels.Font.Size = 8
If a.HasTitle Then
a.AxisTitle.Font.Size = 8
End If
Next

also declare a, Dim a as Axis

You might still need an error handler. An axis might exist with all the
ticklabels properties but if these are not displayed it will fail, eg with
datalabels and some other scenarios.

FWIW can't use "For Each a In cht.Axes" in xl97, need to check if each axis
exists first.

Regards,
Peter T


"navin" wrote in message
ups.com...
Hi,

i have some 20 charts in a sheet along with the data. I want to change
the fonts of all the charts i.e. title, axis etc. i was able to do
this but some of my charts also have secondary axis and font for the
secondary is not changing.

How to achieve this. I used below code to achieve. Please tell where i
am wrong.

Sub Test()
Dim sht As Worksheet
Dim chtObject As ChartObject
Dim cht As Chart



For Each sht In Worksheets
For Each chtObject In sht.ChartObjects
Set cht = chtObject.Chart
''With cht.Axes(xlCategory)
' .TickLabels.Font.Size = 8
' .AxisTitle.Font.Size = 8
' End With
'On Error Resume Next
For Each a In cht.Axes
With cht.Axes(xlValue)
.TickLabels.Font.Size = 8
.AxisTitle.Font.Size = 8
End With

Next a
With cht.DataTable
.Font.Size = 8
End With
With cht.ChartTitle
.Font.Size = 8
End With
Next chtObject
End Sub

Thanks for the help in advance.

Navin