Access 2007：通过vba使用0值隐藏图表对象上的数据标签？ [英] Access 2007: Hide Data Labels on Chart Object via vba with 0 values?

问题描述

我在Access 2007中有一个表格，其中包含一个根据当前日期动态生成的堆积条形图对象，并输出图表的PDF。

I have a form in Access 2007 with a Stacked Bar Chart Object that is dynamically generated depending on the current date and outputs a PDF of the chart.

并且工作正常，但是发生的是正在应用的数据标签，甚至对于具有Null或0值的序列。

Everything generates and works fine, but what is happening is data labels are being applied even for series with a Null or 0 value. This leads to a mess of text in various places.

我正在寻找一种通过VBA删除属于没有值的系列的标签的方法。

I'm looking for a way via VBA to remove any labels that belong to a series with no values.

我试图排除SQL查询中的空值，并设置格式选项，以便0值不会显示。我已经尝试循环通过系列和应用标签，如果值> 0，但如果我设置它应用系列名称，它仍然把它的空白值。

I've tried ruling out null values from the SQL query and also setting the format options so the 0 values won't show. I have tried looping through the series and applying a label if the value is > 0, but if I set it to apply the series name it still puts it for blank values.

编辑当前代码：

Option Compare Database

Private Sub Form_Load()
Dim tstChart As Graph.Chart

   On Error GoTo Form_Load_Error

Set tstChart = [Forms]!testing!barEquip.Object
With tstChart
    .HasTitle = True
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = VBA.Strings.MonthName(VBA.DatePart("m", VBA.Date()) - 1) & " " &     VBA.DatePart("yyyy", VBA.Date()) & _
                        " Test Title"

    For Each srs In .SeriesCollection
        For Each pt In srs.Points
            pt.DataLabel.Text = "Y"
        Next
    Next
End With

   On Error GoTo 0
   Exit Sub

Form_Load_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of VBA Document Form_testing"

End Sub

我可以更改每个标签，但我似乎无法找到一种方法来检查系列点中的每个点。

I'm able to change each label, but I can't seem to figure out a way to check each point in the series points.

编辑：
已解决
（简单，但效果很好）

SOLVED (Simple, but works fine)

Sub AdjustDataLabels(cht As Chart)

Dim srs As Series
Dim pt As Point
Dim vals As Variant

For Each srs In cht.SeriesCollection
    'Apply Value labels
    srs.ApplyDataLabels (xlDataLabelsShowValue)

    For Each pt In srs.Points
        'Check for empty labels
        If pt.DataLabel.Text = "" Then
           'Do nothing
        Else
           'Add Series Name then remove Value
           pt.DataLabel.ShowSeriesName = True
           pt.DataLabel.ShowValue = False
        End If
    Next
Next
End Sub

推荐答案

您正在使用 Graph.Chart 而不是图表。他们在你能用他们做什么更受限制，这是我害怕。

You are using a Graph.Chart instead of a Chart. They are more limited in what you can do with them, which is what I was afraid of. But perhaps this can help anyways.

这个想法是首先确保正在显示系列数据标签。

The idea is to first ensure that the series data labels are being displayed.

一旦我们知道它们被显示，迭代点并选择性地操作点的 DataLabel.Text 属性，基于它 DataLabel.Text 属性。我假设这里显示的值是 0 ，并且你只是想隐藏标签，如果它 0

Once we know they are displayed, iterate the points and selectively manipulate the point's DataLabel.Text property, based on it's DataLabel.Text property. I'm assuming the value here being displayed is 0, and that you simply want to hide labels if it's 0, and do nothing to the other labels.

在您的程序中，我们将调用另一个子执行此操作：

Within your procedure we will call another sub to do this:

Set tstChart = [Forms]!testing!barEquip.Object
With tstChart
    .HasTitle = True
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = VBA.Strings.MonthName(VBA.DatePart("m", VBA.Date()) - 1) & " " &     VBA.DatePart("yyyy", VBA.Date()) & _
                        " Test Title"

    Call AdjustDataLabels(tstChart) 'Call a procedure to modify the labels as needed

End With

现在代码将调用另一个子过程：

So that code will now call on another sub-procedure:

Sub AdjustDataLabels(cht As Graph.Chart)

Dim srs As Graph.Series
Dim pt As Graph.Point
Dim vals As Variant

For Each srs In cht.SeriesCollection
    'First, ensure the dataLabels are ON
    srs.ApplyDataLabels
    For Each pt In srs.Points
        'Now, check the datalabels one by one, testing for your criteria
        If pt.DataLabel.Text = " some condition " Then
            'Criteria met, so blank out this datalabel
            'pt.HasDataLabel = False
            'OR:
             pt.DataLabel.Text = vbNullString

        Else
            'If you need to make any adjustments to other labels, 
            ' you can do that here.
            ' For example you could simply append the series name.
            ' Modify as needed.
            pt.DataLabel.Text = pt.DataLabel.Text & " -- " & srs.Name


        End If
    Next
Next
End Sub

这篇关于Access 2007：通过vba使用0值隐藏图表对象上的数据标签？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！