Updating data labels using Value from Cells Option

Question

Are there ways to update data labels of Stacked Column Chart by using Value from Cells option in VBA macros?

The chart will be showing volumes in axis and percentage in each segment of the stacked bar chart.

This code works on a single segment only. I need it for each segment of the stacked bar chart from Strongly agree to Strongly Disagree.

Sub labelvaluesfromcells()
Dim myrnglabel As Range
Dim labelrange As String
Dim mychart As Chart

    Set myrnglabel = Application.InputBox(Prompt:="Select range label", _
    Title:="Range label", Type:=8)
    labelrange = "='" & ActiveSheet.Name & "'!" & myrnglabel.Address

    Set mychart = ActiveChart
    On Error GoTo Error 
    mychart.FullSeriesCollection("Strongly Agree").Format.Line.Visible = msoFalse
    mychart.FullSeriesCollection("Strongly Agree").ApplyDataLabels 
    mychart.SeriesCollection("Strongly Agree").DataLabels.Format.TextFrame2.TextRange.InsertChartField  msoChartFieldRange, labelrange, 0
    mychart.SeriesCollection("Strongly Agree").DataLabels.ShowRange = True
    mychart.SeriesCollection("Strongly Agree").DataLabels.ShowValue = False
    mychart.SeriesCollection("Strongly Agree").DataLabels.Position = xlLabelPositionCenter

Exit Sub
Error: MsgBox ("Select a chart first!")
End Sub

Answer 1

The following code assumes that the user selects the range from Strongly Agree to Strongly Disagree. However, I would suggest that you add the appropriate error handling to make sure that correct range is selected. Or, alternatively, avoid having the user select the range and simply specify the desired range.

Sub labelvaluesfromcells()

    Dim myrnglabel As Range
    Dim i As Long
    
    If TypeName(ActiveChart) <> "Chart" Then
        MsgBox "Select a chart first!", vbExclamation
        Exit Sub
    End If
    
    On Error Resume Next
    Set myrnglabel = Application.InputBox(Prompt:="Select range label", Title:="Range label", Type:=8)
    If myrnglabel Is Nothing Then Exit Sub 'user cancelled
    On Error GoTo 0
    
    With ActiveChart
        For i = 1 To .FullSeriesCollection.Count
            With .FullSeriesCollection(i)
                .ApplyDataLabels
                With .DataLabels
                    .Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, myrnglabel.Columns(i).Address(, , , True), 0
                    .ShowRange = True
                    .ShowValue = False
                    .Position = xlLabelPositionCenter
                End With
            End With
        Next i
    End With
    
End Sub