VBA to change Chart fonts on all charts in a sheet

[FinUser]

Hi....I have a macro that loops through all the charts on a sheet updating the font and font size on the chart elements. However, I link each chart titles to specific cells so they are dynamically updated. When I use the macro however it hard codes the link. Any idea how I could tweak the code (below) to stop that happening? Many thanks

Sub ChangeFont()



Dim chrtObj As chartObject

Dim sr As series



For Each chrtObj In ActiveSheet.ChartObjects

With chrtObj.Chart



If .HasTitle Then

.ChartTitle.Format.TextFrame2.TextRange.Font.Name = "Arial"

.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 9

End If



' Change font for axis tick labels if present

If .HasAxis(xlCategory, xlPrimary) Then

.Axes(xlCategory, xlPrimary).TickLabels.Font.Name = "Arial"

.Axes(xlCategory, xlPrimary).TickLabels.Font.Size = 9

End If



' Change font for Secondary axis tick labels if present

If .HasAxis(xlCategory, xlSecondary) Then

.Axes(xlCategory, xlSecondary).TickLabels.Font.Name = "Arial"

.Axes(xlCategory, xlSecondary).TickLabels.Font.Size = 9

End If

If .HasAxis(xlValue, xlPrimary) Then

.Axes(xlValue, xlPrimary).TickLabels.Font.Name = "Arial"

.Axes(xlValue, xlPrimary).TickLabels.Font.Size = 9

End If



' Change font for Secondary axis tick labels if present

If .HasAxis(xlValue, xlSecondary) Then

.Axes(xlValue, xlSecondary).TickLabels.Font.Name = "Arial"

.Axes(xlValue, xlSecondary).TickLabels.Font.Size = 9

End If





' Change font for legend if present

If .HasLegend Then

.Legend.Font.Name = "Arial"

.Legend.Font.Size = 9

End If

End With

Next chrtObj





End Sub

 

[Domenic]

Try the following...

If .HasTitle Then

    Dim formula As String
    formula = .ChartTitle.formula

    .ChartTitle.Format.TextFrame2.TextRange.Font.Name = "Arial"
    
    .ChartTitle.Format.TextFrame2.TextRange.Font.Size = 9
    
    .ChartTitle.formula = formula

End If

Hope this helps!

 

[FinUser]

Try the following...

If .HasTitle Then

    Dim formula As String
    formula = .ChartTitle.formula

    .ChartTitle.Format.TextFrame2.TextRange.Font.Name = "Arial"
   
    .ChartTitle.Format.TextFrame2.TextRange.Font.Size = 9
   
    .ChartTitle.formula = formula

End If

Hope this helps!

Hero! Thank you!

 

[Domenic]

You're very welcome, cheers!