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?
 
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
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.