CODEWITHSUNDEEP
vbaexcelcharts
wpfwannabe
wpfwannabe

Reputation: 14877

Excel charts: changing font name of entire chart also makes unwanted font size change

I have a really stubborn problem with Excel charts. See the attached images. Let's say you have a chart designed with all the details and various elements with different font sizes. Applying a new font on the chart actually affects not just the font names of all inner elements but also their size. All elements are made the same size. This is totally unwanted behavior and utterly strange.

Is there a way to affect this behavior globally (with some Excel or chart specific option or similar)?

I need to do this programmatically via VBA but it also happens if one tries it manually too.

Here is the code captured as I record the macro for the chart font change.

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font
        .NameComplexScript = "Arial Narrow"
        .NameFarEast = "Arial Narrow"
        .Name = "Arial Narrow"
    End With
End Sub

Before:

Before

After

After

I just want to change the font name but keep the sizes intact. Any ideas?

Upvotes: 1

Views: 242

Answers (1)

Vulthil
Vulthil

Reputation: 768

I haven't tested if it works, but if you start by saving the font size of the TickLabels on the value axis in a variable, and then applying it at the end of your code.

Sub test()
Dim fontSize as double    


fontSize = ActiveSheet.Shapes("Chart1").Chart.Axes(xlValue).TickLabels.Font.Size

' Your code here    


ActiveSheet.Shapes("Chart1").Chart.Axes(xlValue).TickLabels.Font.Size = fontSize

End Sub

You will be able to access all the font size properties on the chart in the same way, and applying them after your code.

That's what I meant in my comments, but like I said, there might be a more qualified solution, and I'm not sure it will work.

Upvotes: 0

Related Questions