Edit Charts in VBA

[Big Bruiser]

I would like to create a macro that looks at my entire workbook and changes the formats for each chart. The workbook has several tabs with graphs in each tab so it would be helpful to change them all with a press of a button.

Here's what I have started

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim ch As ChartObject
    Dim ax As Axis
 
    For Each ws In ActiveWorkbook.Worksheets
        For Each ch In ws.ChartObjects
            With ch.Chart
                .HasTitle = False
                If .ChartType = xlPie Then .SetElement (msoElementDataLabelInsideEnd)
                .PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
                .ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
 
            End With
        Next ch
    Next ws
 
End Sub

I would like to add the following:

if charttype = xlpie then
data label format = percentage
font = arial bold size 10


if chartype = clustered bar then
Series #1 fill should be RGB (78, 38, 131)
Series #2 fill should be RGB (207, 111, 25)
vertical gridlines = true
axes labels should be arial size 14

if charttype = clustered column then
horizontal gridlines = true
axes labels should be arial size 8
interval between labels = automatic

Thanks for your help!! Let me know if I can help in any way. I used an old macro and was able to add some of the formatting by looking at a recorded macro, but I got errors if I tried adding any of the above. Thanks!

 

[Big Bruiser]

Hey guys, here's what I have come up with so far. Let me know if you have further input.

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim ch As ChartObject
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each ch In ws.ChartObjects
            With ch.Chart
                    .PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
                    .ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
                    .HasTitle = False
                If .ChartType = xlPie Then
                    .SetElement (msoElementDataLabelInsideEnd)
                    .SeriesCollection(1).DataLabels.Select
                    Selection.ShowPercentage = True
                    Selection.ShowValue = False
                    .SeriesCollection(1).HasLeaderLines = False
                    Selection.Font.Size = 10
                    Selection.Font.Name = "arial"
                    Selection.Font.Bold = True
                End If
                If .ChartType = xlColumnClustered Then
                    .Axes(xlCategory).Select
                    .Axes(xlCategory).TickLabelSpacingIsAuto = True
                    .Axes(xlCategory).TickLabels.Font.Size = 8
                    .Axes(xlCategory).TickLabels.Font.Name = "arial"
                    .Axes(xlValue).Select
                    .Axes(xlValue).TickLabels.Font.Size = 8
                    .Axes(xlValue).TickLabels.Font.Name = "arial"
                    .PlotArea.Top = 15
                    .PlotArea.Height = 250
                End If
                If .ChartType = xlColumnStacked Then
                    .Axes(xlCategory).Select
                    .Axes(xlCategory).TickLabels.Font.Size = 10
                    .Axes(xlCategory).TickLabels.Font.Name = "arial"
                    .Axes(xlValue).Select
                    .Axes(xlValue).TickLabels.Font.Size = 10
                    .Axes(xlValue).TickLabels.Font.Name = "arial"
                End If
                If .ChartType = xlBarClustered Then
                    .Axes(xlCategory).Select
                    .Axes(xlCategory).TickLabels.Font.Size = 10
                    .Axes(xlCategory).TickLabels.Font.Name = "arial"
                    .Axes(xlValue).Select
                    .Axes(xlValue).TickLabels.Font.Size = 10
                    .Axes(xlValue).TickLabels.Font.Name = "arial"
                    .SeriesCollection(1).DataLabels.Select
                End If
                    
            End With
        Next ch
    Next ws
    
End Sub

 

[Big Bruiser]

Any ideas how to ignore series if they do not exist? I figured out how to change the color of the series 1 bar, but if there is only 1 bar my macro crashes when it tries to color the 2nd series.

                    .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(78, 38, 131)
                    .SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(207, 111, 25)

 

[Big Bruiser]

Here is my final solution

rivate Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim ch As ChartObject
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each ch In ws.ChartObjects
            With ch.Chart
                    .PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
                    .ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
                    .HasTitle = False
                If .ChartType = xlPie Then
                    .SetElement (msoElementDataLabelInsideEnd)
                    .SeriesCollection(1).DataLabels.Select
                    Selection.ShowPercentage = True
                    Selection.ShowValue = False
                    .SeriesCollection(1).HasLeaderLines = False
                    Selection.Font.Size = 10
                    Selection.Font.Name = "arial"
                    Selection.Font.Bold = True
                End If
                If .ChartType = xlColumnClustered Then
                    .Axes(xlCategory).Select
                    .Axes(xlCategory).TickLabelSpacingIsAuto = True
                    .Axes(xlCategory).TickLabels.Font.Size = 8
                    .Axes(xlCategory).TickLabels.Font.Name = "arial"
                    .Axes(xlValue).Select
                    .Axes(xlValue).TickLabels.Font.Size = 8
                    .Axes(xlValue).TickLabels.Font.Name = "arial"
                    .PlotArea.Top = 15
                    .PlotArea.Height = 250
                End If
                If .ChartType = xlColumnStacked Then
                    .Axes(xlCategory).Select
                    .Axes(xlCategory).TickLabels.Font.Size = 10
                    .Axes(xlCategory).TickLabels.Font.Name = "arial"
                    .Axes(xlValue).Select
                    .Axes(xlValue).TickLabels.Font.Size = 10
                    .Axes(xlValue).TickLabels.Font.Name = "arial"
                End If
                If .ChartType = xlBarClustered Then
                    .Axes(xlCategory).Select
                    .Axes(xlCategory).TickLabels.Font.Size = 10
                    .Axes(xlCategory).TickLabels.Font.Name = "arial"
                    .Axes(xlValue).Select
                    .Axes(xlValue).TickLabels.Font.Size = 10
                    .Axes(xlValue).TickLabels.Font.Name = "arial"
                    .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(78, 38, 131)
                    If .SeriesCollection.Count > 1 Then
                    .SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(207, 111, 25)
                    End If
                End If
                    
            End With
        Next ch
    Next ws
    
End Sub

 

[ttbuson]

Does anyone have any idea why the following code isn't working? I try compiling it and it tells me Runtime error 424 Object Required. I'm trying to get all charts in the worksheet to have helvetica font...

Sub ChangeFont()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Sheets
    WS.Cells.Font.Name = "Helvetica"
Next
Dim CH As ChartObject
For Each CH In WS
    With CH.Chart
    Selection.Font.Name = "Helvetica"
    End With
Next
End Sub

Thanks!

 

[NdNoviceHlp]

For Each ch In ws.ChartObjects
Dave
ps.Don't use selection. It also probably would have been better to start a new thread.