Charts, Help!

[cdegar01]

Can anyone tell me what I'm doing wrong? I'm trying to set the font
size for the axis ticklabels to 6 for every chart in the active sheet.

Sub SetFontSizeAllCharts()
Dim myCht As ChartObject
For Each myCht In ActiveSheet.ChartObjects
myCht.Chart.TickLabels.Font.Size = 6
Next
End Sub

I get errors! Help!

 

[Andy Pope]

Hi cdegar01,

The problem is that you are not including the Axes object.

Explanation extracted from the help file:

expression.Axes(Type, AxisGroup)

Type Optional Variant. Specifies the axis to return. Can be one of the
following XlAxisType constants: xlValue, xlCategory, or xlSeriesAxis
(xlSeriesAxis is valid only for 3-D charts).

AxisGroup Optional Variant. Specifies the axis group. Can be one of
the following XlAxisGroup constants: xlPrimary or xlSecondary. If this
argument is omitted, the primary group is used. 3-D charts have only one
axis group.

So your code should look something like this,

myCht.Chart.Axes(1).TickLabels.Font.Size = 6

Can anyone tell me what I'm doing wrong? I'm trying to set the font
size for the axis ticklabels to 6 for every chart in the active sheet.

Sub SetFontSizeAllCharts()
Dim myCht As ChartObject
For Each myCht In ActiveSheet.ChartObjects
myCht.Chart.TickLabels.Font.Size = 6
Next
End Sub

I get errors! Help!

--

Cheers
Andy

http://www.andypope.info

 

[cdegar01]

Thanks a lot Andy

 

[cdegar01]

I ran this on another page


Sub SetFontSizeAllCharts()

Dim myCht As ChartObject
For Each myCht In ActiveSheet.ChartObjects
myCht.Chart.Axes(1).TickLabels.Font.Size = 6
Next
End Sub


and I get the error "Unable to get the TickLabels property of the Axis
class"

 

[cdegar01]

It works on pages with only about 7 charts, but I want to run it on a
page with 150 charts and it gives that error.

 

[Andy Pope]

I would imagine that is because the chart doesn't have ticklabels.
Use a bit of condition code to check the axis first.

Sub SetFontSizeAllCharts()

Dim myCht As ChartObject
For Each myCht In ActiveSheet.ChartObjects
If myCht.Chart.HasAxis(1) Then
myCht.Chart.Axes(1).TickLabels.Font.Size = 6
End If
Next
End Sub

I ran this on another page


Sub SetFontSizeAllCharts()

Dim myCht As ChartObject
For Each myCht In ActiveSheet.ChartObjects
myCht.Chart.Axes(1).TickLabels.Font.Size = 6
Next
End Sub


and I get the error "Unable to get the TickLabels property of the Axis
class"

--

Cheers
Andy

http://www.andypope.info

 

[cdegar01]

Now it says, "Method 'HasAxis' of object '_Chart' faile

 

[Andy Pope]

Try using a normal For Next loop instead of a For Each.


Sub SetFontSizeAllCharts()
Dim intIndex As Integer
Dim myCht As ChartObject
For intIndex = 1 To ActiveSheet.ChartObjects.Count
Set myCht = ActiveSheet.ChartObjects(intIndex)
If myCht.Chart.HasAxis(1) Then
myCht.Chart.Axes(1).TickLabels.Font.Size = 6
End If
Next
End Sub

It works on pages with only about 7 charts, but I want to run it on a
page with 150 charts and it gives that error.

--

Cheers
Andy

http://www.andypope.info

 

[cdegar01]

I just realized that I have a bunch of pie charts in this page; the
don't have any axes, obviously. ... any idea of how to handle these?

Thanks for your hel

 

[cdegar01]

Using a normal For next loop still gives the same error. It ha
something to do with the pie charts

 

[Andy Pope]

Hi,

Check the chart type first. I've also included the doughnut chart types.

Sub SetFontSizeAllCharts()
Dim myCht As ChartObject

For Each myCht In ActiveSheet.ChartObjects
Select Case myCht.Chart.Type
Case xlPie, xl3DPie, xlPieExploded, xlPieOfPie
' ignore
Case xlDoughnut, xlDoughnutExploded
' ignore
Case Else
If myCht.Chart.HasAxis(1) Then
myCht.Chart.Axes(1).TickLabels.Font.Size = 6
End If
End Select
Next
End Sub

I just realized that I have a bunch of pie charts in this page; they
don't have any axes, obviously. ... any idea of how to handle these?

Thanks for your help

--

Cheers
Andy

http://www.andypope.info

 

[cdegar01]

It still says, "Unable to set the size property of the font class."
There are about 40 of them, and they are pie charts, upper left chart
sub-type (basic pie chart).

Thanks for your help

 

[Jon Peltier]

Try this:

On Error Resume Next
myCht.Chart.Axes(1).TickLabels.Font.Size = 6
On Error Goto 0 ' zero, not oh

If there's an error, it'll skip the offending step.

- Jon