Macro to look thru excel charts and change font/font size etc

[FinUser]

Hi, I am trying to find a macro that loops thru all the many charts (100s) on an active sheet in excel and changes the font on the title, axis, legend and any data labels to one that I specify (i.e. specify the font, the size, formatting like bold etc). Help would be hugely appreciated....

 

[NdNoviceHlp]

Hi FinUser. Here's a link.... Chart Elements in Excel VBA (Part 1) - Chart Title, Chart Area, Plot Area, Chart Axes
Here's some example code...

ChtCnt = ActiveSheet.ChartObjects.Count
For Cnt = 1 To ChtCnt
Set CurrentChart = ActiveSheet.ChartObjects(Cnt).Chart
With CurrentChart
.HasTitle = True
.ChartTitle.AutoScaleFont = False
.ChartTitle.Characters.Text = "CHART " & ChtCnt
.ChartTitle.Characters.Font.Size = 12
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.AutoScaleFont = False
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X"
.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 12
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.AutoScaleFont = False
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y"
.Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 12
End With

With CurrentChart.SeriesCollection(1)
.Border.ColorIndex = 1
.Border.Weight = xlMedium
.Border.LineStyle = xlContinuous
.MarkerStyle = xlMarkerStyleNone
End With

With CurrentChart.SeriesCollection(1).DataLabels
.NumberFormat = "0.0"
.VerticalAlignment = xlCenter
.Position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 12
.Font.Bold = True
.Font.ColorIndex = 3
End With
'CurrentChart.SeriesCollection(1).Points(1).DataLabel.Delete
Next Cnt

HTH. Dave

 

[Domenic]

Try the following macro (change the name and size of the font as desired) . . .

Option Explicit

Sub FormatAllChartsOnActivesheet()

    Dim chrtObj As ChartObject
    Dim sr As Series
    
    For Each chrtObj In ActiveSheet.ChartObjects
        With chrtObj.Chart
            If .HasTitle Then
                With .ChartTitle.Format.TextFrame2.TextRange.Font
                    .Name = "Arial"
                    .Size = 12
                    .Bold = True
                End With
            End If
            With .Axes(Type:=xlValue).TickLabels.Font
                .Name = "Arial"
                .Size = 10
                .Bold = True
            End With
            With .Axes(Type:=xlCategory).TickLabels.Font
                .Name = "Arial"
                .Size = 10
                .Bold = True
            End With
            If .HasLegend Then
                With .Legend.Format.TextFrame2.TextRange.Font
                    .Name = "Arial"
                    .Size = 10
                    .Bold = msoTrue
                End With
            End If
            For Each sr In .SeriesCollection
                With sr
                    If .HasDataLabels Then
                        With .DataLabels.Format.TextFrame2.TextRange.Font
                            .Name = "Arial"
                            .Size = 9
                            .Bold = msoTrue
                        End With
                    End If
                End With
            Next sr
        End With
    Next chrtObj

End Sub

Hope this helps!

 

[FinUser]

Try the following macro (change the name and size of the font as desired) . . .

Option Explicit

Sub FormatAllChartsOnActivesheet()

    Dim chrtObj As ChartObject
    Dim sr As Series
   
    For Each chrtObj In ActiveSheet.ChartObjects
        With chrtObj.Chart
            If .HasTitle Then
                With .ChartTitle.Format.TextFrame2.TextRange.Font
                    .Name = "Arial"
                    .Size = 12
                    .Bold = True
                End With
            End If
            With .Axes(Type:=xlValue).TickLabels.Font
                .Name = "Arial"
                .Size = 10
                .Bold = True
            End With
            With .Axes(Type:=xlCategory).TickLabels.Font
                .Name = "Arial"
                .Size = 10
                .Bold = True
            End With
            If .HasLegend Then
                With .Legend.Format.TextFrame2.TextRange.Font
                    .Name = "Arial"
                    .Size = 10
                    .Bold = msoTrue
                End With
            End If
            For Each sr In .SeriesCollection
                With sr
                    If .HasDataLabels Then
                        With .DataLabels.Format.TextFrame2.TextRange.Font
                            .Name = "Arial"
                            .Size = 9
                            .Bold = msoTrue
                        End With
                    End If
                End With
            Next sr
        End With
    Next chrtObj

End Sub

Hope this helps!

This is brilliant! Thank you very much

 

[FinUser]

Hi FinUser. Here's a link.... Chart Elements in Excel VBA (Part 1) - Chart Title, Chart Area, Plot Area, Chart Axes
Here's some example code...

ChtCnt = ActiveSheet.ChartObjects.Count
For Cnt = 1 To ChtCnt
Set CurrentChart = ActiveSheet.ChartObjects(Cnt).Chart
With CurrentChart
.HasTitle = True
.ChartTitle.AutoScaleFont = False
.ChartTitle.Characters.Text = "CHART " & ChtCnt
.ChartTitle.Characters.Font.Size = 12
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.AutoScaleFont = False
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X"
.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 12
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.AutoScaleFont = False
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y"
.Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 12
End With

With CurrentChart.SeriesCollection(1)
.Border.ColorIndex = 1
.Border.Weight = xlMedium
.Border.LineStyle = xlContinuous
.MarkerStyle = xlMarkerStyleNone
End With

With CurrentChart.SeriesCollection(1).DataLabels
.NumberFormat = "0.0"
.VerticalAlignment = xlCenter
.Position = xlLabelPositionBelow
.Orientation = xlHorizontal
.AutoScaleFont = False
.Font.Size = 12
.Font.Bold = True
.Font.ColorIndex = 3
End With
'CurrentChart.SeriesCollection(1).Points(1).DataLabel.Delete
Next Cnt

HTH. Dave

Great, thank you!

 

[smozgur]

This is brilliant! Thank you very much

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.