Set font on pivot charts to Arial

Question

I am trying to update the font of the data labels on various pivot charts on the sheet of my workbook titled Dashboard.

I need to the chart data labels set to font Arial size 12 and the legend font set to Arial size 10.5.

I receive

Run-time error '91':
Object variable or With block variable not set

This is the code that I came up with.

Sub DASHfontsize()
' DASHfontsize Macro
' change the font on labels to arial 12 and the legend to arial 10.5
ActiveChart.ChartArea.Select
With Selection.Format.TextFrame2.TextRange.Font
    .NameComplexScript = "Arial"
    .NameFarEast = "Arial"
    .Name = "Arial"
End With
Selection.Format.TextFrame2.TextRange.Font.Size = 12

ActiveChart.Legend.Select
With Selection.Format.TextFrame2.TextRange.Font
    .NameComplexScript = "Arial"
    .NameFarEast = "Arial"
    .Name = "Arial"
End With
Selection.Format.TextFrame2.TextRange.Font.Size = 10.5
End Sub

By "this" I assume you mean @Sam's answer. In which case you should mark it as correct (click on the check mark to the left of it) and consider upvoting it. — cybernetic.nomad, Nov 04 '19 at 15:47

score 0 · Accepted Answer · answered Oct 31 '19 at 20:55

If you find the word Active or Selection in your macros, you have a risk of exactly this. A bit of speculation - you have recorded a macro, and had the chart selected prior to starting the recording.
The way around this is to rewrite your code to make a bit more safe.

Dim TargetChart As Chart
Set TargetChart = Worksheets("Sheet1").ChartObjects(1).Chart
With TargetChart.ChartArea.Format.TextFrame2.TextRange.Font
    .NameComplexScript = "Arial"
    .NameFarEast = "Arial"
    .Name = "Arial"
End With

The TargetChart variable points to the first chart on Sheet1. Adjust as needed.

Set font on pivot charts to Arial

1 Answers1