Search code examples
excelvbapivot-chart

Set font on pivot charts to Arial

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

enter image description here

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
Solution

  • 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.