Check for the existence of data labels
 

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

Check for the existence of data labels
 

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.

Check for the existence of data labels
 

On 7 Mrz., 22:24, Wouter HM wrote:
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