Changing the font size of Axis Tick Labels in a Pivot Chart

[curtishavak]

Does anyone know how (or if) I can change the default font size of the tick labels on the vertical axis in a Pivot Chart? This is easy to do in a standard chart, but for some reason, the option isn't presented (at least not obviously) on a PivotChart.

I searched the interwebs and found the below bit of code and tried it, but it gave an error:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">With ActiveChart.Axes(xlCategory).TickLabels.Font
'.Bold = msoTrue
.Size =18
EndWith</code>

Any ideas?

 

[RickXL]

Hi,

You need a space between End and With:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">With ActiveChart.Axes(xlCategory).TickLabels.Font
        '.Bold = msoTrue
        .Size =18
End With

Which reminds me of the old joke about there being too much space between pig and and and and and whistle.

</code>

 

[curtishavak]

Well now I just feel kinda silly... Sometimes you just need a fresh pair of eyes.

I'll test and see if that does the trick.

 

[curtishavak]

Well, it sort of worked. I'm using a format with a data table at the bottom of the chart. When I run the macro, the height of the box containing the primary horizontal axis labels increases, but the font of the labels themselves don't change size.

So, any other ideas?

Also, I don't want to change the font size of the horizontal axis, but of the primary and secondary axes. I'm not familiar with the VBA to define which axis I want to change.

 

[RickXL]

To find how Excel does it try using the macro recorder.

Switch it on.
Make the change.
Switch it off.
Look at the code produced.

changed the x-axis then the y-axis and got this:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveChart.Axes(xlCategory).Select
    Selection.Format.TextFrame2.TextRange.Font.Size = 24
    ActiveChart.Axes(xlValue).Select
    Selection.Format.TextFrame2.TextRange.Font.Size = 16
End Sub

 

[curtishavak]

Good idea...it's been a while for me since the last time I wrote a macro. Forgot about that nifty tool.

I recorded a macro on a standard chart and got the same code. Then tried running it on a pivot chart and it gave me an error.

Then, EUREKA! I happened to glance at the Home Ribbon while I had an axis selected and the Font options and noticed they weren't grayed out. I increased the font size in the ribbon and it worked!

Didn't see the forest for the trees...

Thanks for the help, Rick!

 

[RickXL]

No problem. I am glad you got it sorted in the end.

Thanks for letting me know.

Regards,