Edit all Data Labels's Label Options's Label Contains together in Chart

[naveyafluz]

When I add Data Labels in my Chart, the Label Contains will show Value and Show Leader Lines by default. I want to change the Label Contains to show Series Name and Value instead but I have to do it one by one.

Any Excel experts know how to change it all at once to show the results as below?

 

[John_w]

Try this macro. It changes the data labels for all the charts on the active sheet.

Public Sub Change_Charts_Data_Labels()

    Dim ws As Worksheet
    Dim thisChartObject As ChartObject
    Dim thisSeries As Series
    Dim thisDataLabel As DataLabel
    
    Set ws = ActiveSheet
    
    For Each thisChartObject In ws.ChartObjects
        For Each thisSeries In thisChartObject.Chart.FullSeriesCollection
            thisSeries.HasDataLabels = True
            thisSeries.HasLeaderLines = False
            For Each thisDataLabel In thisSeries.DataLabels
                thisDataLabel.ShowSeriesName = True
                thisDataLabel.ShowValue = True
            Next
        Next
    Next
    
End Sub

 

[naveyafluz]

Try this macro. It changes the data labels for all the charts on the active sheet.

Public Sub Change_Charts_Data_Labels()

    Dim ws As Worksheet
    Dim thisChartObject As ChartObject
    Dim thisSeries As Series
    Dim thisDataLabel As DataLabel
   
    Set ws = ActiveSheet
   
    For Each thisChartObject In ws.ChartObjects
        For Each thisSeries In thisChartObject.Chart.FullSeriesCollection
            thisSeries.HasDataLabels = True
            thisSeries.HasLeaderLines = False
            For Each thisDataLabel In thisSeries.DataLabels
                thisDataLabel.ShowSeriesName = True
                thisDataLabel.ShowValue = True
            Next
        Next
    Next
   
End Sub

it worked but it applies to all the charts in the sheet. How should I amend the code if I want to use this code on an active chart instead of active sheet?

I tried changing Set ws = ActiveSheet to Set ws = ActiveChart but it cannot work even on the particular chart.

 

[John_w]

How should I amend the code if I want to use this code on an active chart instead of active sheet?

Public Sub Change_Active_Chart_Data_Labels()

    Dim thisChart As Chart
    Dim thisSeries As Series
    Dim thisDataLabel As DataLabel
    
    Set thisChart = ActiveChart
    
    If Not thisChart Is Nothing Then
        For Each thisSeries In thisChart.FullSeriesCollection
            thisSeries.HasDataLabels = True
            thisSeries.HasLeaderLines = False
            For Each thisDataLabel In thisSeries.DataLabels
                thisDataLabel.ShowSeriesName = True
                thisDataLabel.ShowValue = True
            Next
        Next
    Else
        MsgBox "No chart is selected", vbExclamation
    End If
    
End Sub