Check for the existence of data labels

[AndreasHermle]

Dear Experts:

below macro ...
.... deletes any data labels from all data series of all charts on the
active worksheet.

I wonder whether the macro could check at the beginning ...
.... for the existence of any data labels (1 to n) and if there are
none at all come up with a msgbox telling the user ('All data labels
have already been deleted!').

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

Regards, Andreas

Sub DataLabels_Remove()

Dim ChtObj As ChartObject
Dim i As Integer
Dim ser As Series
i = 0

For Each ChtObj In ActiveSheet.ChartObjects
With ChtObj.Chart
For Each ser In .SeriesCollection
ser.ApplyDataLabels (xlDataLabelsShowNone)
Next ser
End With
Next

End Sub

 

[Wouter HM]

Hi Andreas,

I am afraid a message before removing the labes will not be posible.
You could check if labels have been deleted with something like:

Sub DataLabels_Remove()
Dim ChtObj As ChartObject
Dim i As Integer
Dim ser As Series
i = 0
Dim hasLabels As Boolean

For Each ChtObj In ActiveSheet.ChartObjects
With ChtObj.Chart
For Each ser In .SeriesCollection
If ser.HasDataLabels Then
hasLabels = True
End If
ser.ApplyDataLabels (xlDataLabelsShowNone)
Next ser
End With
Next
If hasLabels = False Then
MsgBox "Labels already deleted"
End If
End Sub

HTH,

Wouter.

 

[AndreasHermle]

Hi Andreas,

I am afraid a message before removing the labes will not be posible.
You could check if labels have been deleted with something like:

Sub DataLabels_Remove()
   Dim ChtObj As ChartObject
   Dim i As Integer
   Dim ser As Series
   i = 0
   Dim hasLabels As Boolean

   For Each ChtObj In ActiveSheet.ChartObjects
      With ChtObj.Chart
         For Each ser In .SeriesCollection
            If ser.HasDataLabels Then
               hasLabels = True
            End If
            ser.ApplyDataLabels (xlDataLabelsShowNone)
         Next ser
      End With
   Next
   If hasLabels = False Then
      MsgBox "Labels already deleted"
   End If
End Sub

HTH,

Wouter.

Hi Wouter,

great, thank you very much for your professional support. It is
working as desired.

Regards, Andreas