数据标签VBA Excel [英] Datalabels VBA Excel

问题描述

我希望有人可以帮助我进行Excel VBA数据标签查询.

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

在Excel 2016中，我想基于一系列单元格(而不是值)将数据标签添加到折线图中.范围是表格列.

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.

谢谢蚂蚁

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

推荐答案

据我所确定，您需要一个字符串公式，其中包含.Format.TextFrame2.TextRange.InsertChartField行的单元格地址.也许尝试:

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

"='" & ActiveSheet.Name & "'!"为范围的字符串地址获取所需的工作表名称，并带有所需的等号，因为公式是必需的.

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

这篇关于数据标签VBA Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！