cellMatrix.net

VBA for Adding and Deleting Data Labels

I'm currently working on a project where I have multiple embedded line charts in a workbook. Each line chart contains any number of multiple series. Each series contains 12 points representing each month of the year. For each chart I need to show the data points for the current month only.

Although the combination Line - XY Scatter chart works, I want to use VBA to add and delete the data labels in each chart in an effort to minimize the number of chart series that the owner of the workbook needs to maintain. Below are some simple macros for adding and deleting data labels that I've used for reference and / or utilized in my project. Each example works for the active chart only (an active single embedded chart).

To delete a label from a single series:

Sub Delete_Labels_From_A_Single_Series()
    For Each X In ActiveChart.SeriesCollection(1).Points
        X.DataLabel.Delete
    Next X
End Sub

A second method for deleting a label from a single series:

Sub Delete_Labels_From_A_Single_Series()
    
    Dim Cht As Chart
    Dim Srs As Series
    
    Set Cht = ActiveChart
    Set Srs = Cht.SeriesCollection(1)
    
        With Srs
            If .HasDataLabels Then .DataLabels.Delete
        End With

End Sub

To add data labels to multiple series at one time:

Sub Add_Data_Labels_To_All_Series()
    
    Dim Cht As Chart
    
    Set Cht = ActiveChart
    
    For Each Sr In Cht.SeriesCollection
        Sr.ApplyDataLabels
        Sr.DataLabels.ShowValue = True
        Sr.DataLabels.Position = xlLabelPositionAbove
    Next Sr

End Sub

To delete data labels from multiple series at one time:

Sub Delete_Data_Labels_From_All_Series()
    
    Dim Cht As Chart
    
    Set Cht = ActiveChart
    
    For Each Sr In Cht.SeriesCollection
        If Sr.HasDataLabels Then Sr.DataLabels.Delete
    Next Sr

End Sub