Check for existence of data labels (first chart of the active worksheet)

Try something like this:

Sub CheckChartLabels()
    
    Dim cht As Chart
    Dim ser As Series
    Dim HasLabels As Long
    
    Set cht = ActiveSheet.ChartObjects(1).Chart
    For Each ser In cht.SeriesCollection
        If ser.HasDataLabels Then HasLabels = HasLabels + 1
    Next
    
    If HasLabels <> cht.SeriesCollection.Count Then
        MsgBox "Chart has labels for " & HasLabels & " out of " & cht.SeriesCollection.Count & " series", vbExclamation, "Warning!"
    End If
    
End Sub

Select allOpen in new window

try this :

Sub checkChart()
Dim item As Series
Dim result As Boolean
Dim number As Integer

result = True
For Each item In ActiveSheet.Shapes(1).Chart.SeriesCollection
    result = result * item.HasDataLabels
Next item
If Not result Then MsgBox "The first chart does not contain any data labels"

number = 0
For Each item In ActiveSheet.Shapes(1).Chart.SeriesCollection
    If item.HasDataLabels Then number = number + 1
Next item
If number < ActiveSheet.Shapes(1).Chart.SeriesCollection.Count Then MsgBox number & " out of " & ActiveSheet.Shapes(1).Chart.SeriesCollection.Count & " series in the first chart does not have data labels"

End Sub

Select allOpen in new window

Dear Matthews and akoster:

thank you very much for your swift and professional help.
I slightly tweaked akoster's code (it is ChartObjects(1) instead of .Shapes(1))

Both codes basically work fine. Great job. But I am afraid to tell you that both codes do not work under the following condition.

Please have a look at the attached sample file. If the data label is missing - for example - on just one of one (1) data series the macro does not detect this.

Any idea if it is possible to have the macro detect these cases as well.

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

Regards, Andreas
 DataLabels-SampleFile.xlsx

1) I added my code to the sample workbook

2) When I have labels for both series and run my macro, I get no message (by design)

3) When I turn off labels for one series, I get a warning message

4) When I turn off all data labels, I get a warning message


In other words, my macro works :)

Hi Patrick,

wow, really a great job! Exactly what I was looking for. You really deserve your designation as MVP.

Thank you very, very much for your swift and professional help.

I really appreciate it.

Regards, Andreas

This forum deserves its name!

It should be re-named Speedy Experts Exchange!