VBA Chart Formatting to Loop Through all Charts in Workbook

[PippaBett]

I have a large dashboard that once complete will have c300 pie charts and need them all to have specific formatting. I have recorded a macro of me applying the formatting to one chart and looked up a macro to loop through all charts in the workbook and tried to combine the two to create the macro I need but I am getting the error message "Run-time error '-2147467259 (80004005)': Unable to get the Count property of the DataLables class". I have spent hours trying to look for a solution to get this to work without having to resort to manually formatting 300 charts!

The code is
Sub LoopThroughCharts()


Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject

Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet

For Each sht In ActiveWorkbook.Worksheets
For Each cht Insht.ChartObjects
cht.Activate

cht.Chart.SetElement msoElementDataLableBestFit

ActiveChart.FullSeriesCollection(1).DataLabels.Select
WithSelection.Format.Fill
.Visible =msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Solid
End With
WithSelection.Format.Fill
.Visible =msoTrue
.ForeColor.ObjectThemeColor= msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency= 0
.Solid
End With
Selection.Format.TextFrame2.MarginLeft = 0
Selection.Format.TextFrame2.MarginRight = 0
Selection.Format.TextFrame2.MarginTop = 0
Selection.Format.TextFrame2.MarginBottom = 0

Next cht
Next sht

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

Does anyone know how I can fix this? Many thanks in advance

 

[Jon Peltier]

Where does the error occur? I don't even see where you're counting labels.

 

[PippaBett]

The error occurs at the line ActiveChart.FullSeriesCollection(1).DataLabels.Select which is where I selected the data labels in the recorded macro

 

[Jon Peltier]

A big problem with recorded macros is that they repeat all the little mouse actions, like selecting everything. And if for some reason you're not showing data labels, perhaps because the chart's source data range is blank and no points are plotted, trying to select them will cause an error.

Before I fix that, I'll point out that there are two nearly identical blocks of code. The first block fills the label rectangles with a dark color.

WithSelection.Format.Fill
  .Visible =msoTrue
  .ForeColor.ObjectThemeColor = msoThemeColorAccent1
  .ForeColor.TintAndShade = 0
  .ForeColor.Brightness = 0
  .Solid
End With

The second block fills the label rectangles with a light color (white, it looks like).

WithSelection.Format.Fill
  .Visible =msoTrue
  .ForeColor.ObjectThemeColor= msoThemeColorBackground1
  .ForeColor.TintAndShade = 0
  .ForeColor.Brightness = 0
  .Transparency= 0
  .Solid
End With

So the first block should be deleted.

Another problem with the macro recorder is that it includes code that simply applied default properties. So that second block can be cleaned up:

WithSelection.Format.Fill
  .Visible =msoTrue
  .ForeColor.ObjectThemeColor= msoThemeColorBackground1
End With

So now let's try a little cleanup.

  For Each sht In ActiveWorkbook.Worksheets
    For Each cht Insht.ChartObjects
      cht.Activate

      With ActiveChart
        .SetElement msoElementDataLableBestFit

        With .FullSeriesCollection(1).DataLabels
          With .Format.Fill
            .Visible =msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorBackground1
          End With
          With .Format.TextFrame2
            .MarginLeft = 0
            .MarginRight = 0
            .MarginTop = 0
            .MarginBottom = 0
          End With
        End With
      End With
    Next cht
  Next sht

I don't like using .SetElement, either, but that's a tale for another time.

 

[PippaBett]

Hi thanks for your response, unfortunately I am still getting the same error message as before it is now highlighting this portion of text With .FullSeriesCollection(1).DataLabels

 

[Jon Peltier]

I just tested this code on a workbook with multiple worksheets and charts. No errors, each chart ended up with data labels.

Sub UpdateCharts()
  Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
    Dim chtob As ChartObject
    For Each chtob In ws.ChartObjects
      With chtob.Chart
        .SetElement msoElementDataLabelBestFit

        With .FullSeriesCollection(1).DataLabels
          With .Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorBackground1
          End With
          With .Format.TextFrame2
            .MarginLeft = 0
            .MarginRight = 0
            .MarginTop = 0
            .MarginBottom = 0
          End With
        End With
      End With
    Next chtob
  Next ws
End Sub

Does the code work on any of your charts, or does it fail on the first one?

Anything noteworthy about the chart that the code fails on? Does it have data labels? Is it not a pie (so best fit is meaningless)? Does it even have any data?

 

[PippaBett]

Hi - I have now got it to run and data labels are being added but the formatting is not being applied to them - it is no longer returning an error though