Datalabels VBA Excel

Question

I hope someone can help me with an Excel VBA datalabel query.

In Excel 2016 I would like to add datalabels to a line chart based on a range of cells (as opposed to values). The range is a table column.

For some reason the Macro runs without errors, but the datalabels don't display as the range does not capture i.e. once run the selected range for the datalabels remains blank.

Please note I have copy pasted below the relevant part. There are many more series collections in the full code. I am only adding labels to specific ones, however.

Thanks, Ant

Dim created_table_object As ListObject
Dim created_table As String
Set created_table_object = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$26"), , xlNo)
created_table = created_table_object.Name

Dim waterfall_chart As Chart
Set waterfall_chart = ActiveSheet.Shapes.AddChart2(XlChartType:=xlColumnStacked).Chart

With waterfall_chart
    With .FullSeriesCollection(7)
        .Name = "Positive Data Labels"
        .Values = ActiveSheet.ListObjects(created_table).ListColumns(11).DataBodyRange
        .ChartType = xlLine
        .Format.Line.Visible = msoFalse
        .HasDataLabels = True
        With .DataLabels
            .Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, "ActiveSheet.ListObjects(created_table).ListColumns(12).DataBodyRange", 0
            .Format.TextFrame2.TextRange.Font.Size = 12
            .Format.TextFrame2.TextRange.Font.Bold = msoTrue
            .ShowRange = True
            .ShowValue = False
            .NumberFormat = number_format
            .Position = xlLabelPositionAbove
        End With
    End With
End With

I suggest you indent your code for better readability. – teylyn Jan 25 '18 at 19:56 — teylyn, Jan 25 '18 at 19:56

score 0 · Accepted Answer · answered Jan 25 '18 at 22:56

As far as I could determine, you would need a string formula with cell addresses for your .Format.TextFrame2.TextRange.InsertChartField line. Maybe try:

.Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, _
    "='" & ActiveSheet.Name & "'!" & _
    ActiveSheet.ListObjects(created_table).ListColumns(12).DataBodyRange.Address, 0

The "='" & ActiveSheet.Name & "'!" gets the required sheet name for the range's string address with the required equals sign, since a formula's required.

You could also declare a string variable and then use that in the formating line to help the code read clearer:

    Mystr = "='" & ActiveSheet.Name & "'!" & ActiveSheet.ListObjects(created_table).ListColumns(12).DataBodyRange.Address 

   .Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, Mystr, 0

Thank you Mistella, it works! I am still picking up vba and hadn't seen or used .address yet — Anthony Rogers, Jan 26 '18 at 04:57

Datalabels VBA Excel

1 Answers1

Linked