CODEWITHSUNDEEP
excelvbaexcel-charts
Sampi Wu
Sampi Wu

Reputation: 13

Is there a way to extract the Data Label of Chart using VBA?

I would like to extract all the data label if the refer to a range (Value From Cells) of the chart using VBA: so, it should give me something like Chart 1: Data label =Sheet1!$D$2:$D$11 Chart 2: Data label =Sheet1!$E$2:$E$11 Chart 3: Data label None

Sub GetDataLabelFormula()
    Dim chartObj As ChartObject
    Dim chartSeries As Series
    Dim dataLabel As dataLabel
    
    ' Set the chart object and series
    Set chartObj = ActiveSheet.ChartObjects("Chart 1")
    Set chartSeries = chartObj.Chart.SeriesCollection(1)
    
    ' Set the data label
    Set dataLabel = chartSeries.Points(1).dataLabel
    
    ' Retrieve the formula of the data label
    Debug.Print dataLabel.formula
End Sub

But this return me the value instead

Upvotes: 0

Views: 203

Answers (1)

kcp
kcp

Reputation: 77

Sub ExtractDataLabelRanges()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim ser As Series
    Dim dataLabelRange As String
    Dim chartNumber As Integer

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name

    ' Loop through each chart object in the worksheet
    For Each cht In ws.ChartObjects
        chartNumber = chartNumber + 1
        dataLabelRange = "None"

        ' Loop through each series in the chart
        For Each ser In cht.Chart.SeriesCollection
            ' Check if the series has data labels
            If ser.HasDataLabels Then
                ' Loop through each data label
                For Each dl In ser.DataLabels
                    ' Get the text of the data label
                    dataLabelRange = dl.Text
                    ' Output the result
                    Debug.Print "Chart " & chartNumber & ": Data label = " & dataLabelRange
                Next dl
            End If
        Next ser

        ' If no data labels found, output "None"
        If dataLabelRange = "None" Then
            Debug.Print "Chart " & chartNumber & ": Data label = None"
        End If
    Next cht
End Sub

Upvotes: 0

Related Questions