Change font name and size on data labels, category axis and chart title on all charts of the active worksheet using VBA

Dear Experts:

below code changes the font size and font name on all charts of the active worksheet.

I also would like to expand this macro so that also ...
... all the data labels of the charts' data series as well as
... the x-axis (Category Axis) also get changed to Verdana and Font Size 10

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Sub ChartFont()
    Dim Charts As ChartObject
        
    For Each Charts In ActiveSheet.ChartObjects
        Charts.Activate
        ActiveChart.ChartTitle.Font.Name = "Verdana"
        ActiveChart.ChartTitle.Font.Size = 9
   Next Charts
End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Well here it is you had forgot the Legend that I also added you can modify the size if you want.

Sub ChartFont()
'... all the data labels of the charts' data series as well as
' ... the x-axis (Category Axis) also get changed to Verdana and Font Size 10
    Dim Charts As ChartObject
    Dim Lgnd As LegendEntry
    
    For Each Charts In ActiveSheet.ChartObjects
        Charts.Activate
        ActiveChart.ChartTitle.Font.Name = "Verdana"
        ActiveChart.ChartTitle.Font.Size = 9
        
        On Error Resume Next
        With ActiveChart.Axes(xlCategory).TickLabels
            .AutoScaleFont = True
            With .Font
                .Name = "Verdana"
                .Size = 10
            End With
        End With
        
        With ActiveChart.Axes(xlSeriesAxis).TickLabels
            .AutoScaleFont = True
            With .Font
                .Name = "Verdana"
                .Size = 10
            End With
        End With
        
        With ActiveChart.Axes(xlValue).TickLabels
            .AutoScaleFont = True
            With .Font
                .Name = "Verdana"
                .Size = 10
            End With
        End With
        On Error GoTo 0
        
        For Each Lgnd In ActiveChart.Legend.LegendEntries
            Lgnd.Font.Name = "Verdana"
            Lgnd.Font.Size = 10
        Next Lgnd
        
   Next Charts
End Sub

Open in new window



gowflow
0
Andreas HermleTeam leaderAuthor Commented:
Hi gowflow,

thank you very much for your swift support. Everything works fine with the exception of the legend entries, they still feature the default font, i.e. Calibri.

Moreover I would like to change the data labels of the data series, this is is not considered in your code, I am afraid.

Again thank you very much for your great support, it would be great if you could still incorporate my requirements in the code.

Thank you.  

Regards, Andreas
0
gowflowCommented:
I tried the legend here on a chart I created when I did the code and everything worked fine. Unfortunately I deleted the whole thing and just copied the code.

Could you please post a sample workbook this would facilitate my task ?

gowflow
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Andreas HermleTeam leaderAuthor Commented:
Hi gowflow, thank you very much for your reply, will get back to you this afternoon. Thank you again, regards, Andreas
0
gowflowCommented:
ok fine
gowflow
0
Andreas HermleTeam leaderAuthor Commented:
Hi gowflow,

sorry for the delay, Here we are. Thank you very much in advance. Regards, Andreas
Sample-File-Chart-Formatting-VBA.xls
0
gowflowCommented:
yes it works perfect.
pls chk the file. For the legend don't just select the legend box but select the legend item and you will see it has changed font.

gowflow
Sample-File-Chart-Formatting-VBA-V01.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andreas HermleTeam leaderAuthor Commented:
Hi gowflow,

great, thank you very much for your professional help. Works like a charm. I really appreciate it.

Regards, Andreas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.