取消选择标签和图表Excel VBA [英] Deselect Label And Chart Excel VBA

问题描述

我正在使用"Sheet1"上的命令按钮使用VBA创建图表，但是该图表正在添加到另一张工作表("Sheet2")上.

I am creating a chart using VBA using a command button I have on "Sheet1", however the chart is being added to another sheet ("Sheet2").

添加图表之后，我使用下面的代码根据DataLabel值为条形着色并更改DataLabels:

After the chart is added, I am using the below code to color the bars based on the DataLabel values and change the DataLabels as well:

Dim oPoint As Excel.Point
Dim sngPercente As Single

For Each oPoint In Worksheets("Sheet2").ChartObjects("Performance").Chart.SeriesCollection(1).Points
oPoint.DataLabel.Select

sngPercente = CSng(Split(oPoint.DataLabel.Caption, "%")(0))

With oPoint
If sngPercente < 70 Then
.Interior.Color = RGB(255, 0, 0)
End If
If sngPercente > 75 Then
.Interior.Color = RGB(0, 176, 80)
End If
If sngPercente >= 70 And sngPercente <= 75 Then
.Interior.Color = RGB(148, 208, 80)
End If
If sngPercente = 0 Then
.DataLabel.Caption = "OFF"
End If
End With

Next oPoint

运行此代码并转到"Sheet2"后，我注意到图表和其中的最后一个数据标签仍处于选中状态.

After running this code and going to "Sheet2", I notice that the chart and the last datalabel in it are still selected.

_{(来源: gulfup.com )}

如何取消/取消选择此图表?

How do I un/de-select this chart?

这是我尝试过的:

Worksheets("Sheet2").Range("A1").Select

由于正在从另一个工作表运行代码，因此无法正常工作.

Does not work as the code is being run from another sheet.

ActiveChart.Deselect

根本不起作用.

从代码中删除 oPoint.DataLabel.Select 行.

不可能，因为没有它，代码将因运行时错误而失败.

Not possible, because without it the code will fail with a run-time error.

SendKeys "{ESC}"

有效，但高度不可靠，就好像与其他宏一起使用时，它将破坏代码，并且这将给出代码执行已被中断"的提示.错误.

Works, but highly unreliable, as if used with other macros it will break the code, and this will give the "code execution has been interrupted" error.

还有什么可以尝试的吗?

Anything else that I can try?

推荐答案

我将通过读取值而不是标题来完全避免此问题:

I'd avoid the issue completely by reading the values instead of the captions:

Dim ChartRng                    As Range
Dim ser                         As Excel.Series
Set ChartRng = Worksheets("Overview").Range("A1:C19")

Dim oChtObj                     As ChartObject
Set oChtObj = Worksheets("Overview").ChartObjects.Add(Left:=48, Width:=570, Top:=1000, Height:=367)

With oChtObj.Chart
    .Parent.Name = "Performance"
    .ChartType = xlColumnClustered
    .ApplyLayout (1)
    .SetSourceData ChartRng
    .HasLegend = True
    Set ser = .SeriesCollection(1)
    ser.HasDataLabels = True
    .SeriesCollection(2).HasDataLabels = False
    .HasTitle = True
    .ChartTitle.Caption = "Call Facing Time (KPI: 75%) Per Agent"
    .ChartTitle.Font.Size = 16
    .ChartTitle.Font.Color = RGB(84, 84, 84)
    ser.Name = "CFT"
    .SeriesCollection(2).Name = "KPI"
    .SeriesCollection(2).ChartType = xlLine
    .ChartStyle = 26
    .Axes(xlCategory).HasMinorGridlines = False
    .Axes(xlCategory).HasMajorGridlines = False
    .Axes(xlValue).HasMinorGridlines = False
    .Legend.LegendEntries(1).Delete
    .SeriesCollection(2).Border.Color = RGB(37, 64, 97)
    .SeriesCollection(2).Format.Line.Weight = 3
    .Axes(xlValue).TickLabels.Font.Size = 9
    .Axes(xlCategory).TickLabels.Font.Size = 9
    .Axes(xlValue).TickLabels.Font.Color = RGB(77, 77, 77)
    .Axes(xlCategory).TickLabels.Font.Color = RGB(77, 77, 77)
    .Legend.Position = xlBottom
    .Legend.Font.Size = 9
    ser.DataLabels.Font.Size = 9
    .ChartArea.Border.Color = RGB(217, 217, 217)
    .Axes(xlValue).MajorGridlines.Border.Color = RGB(217, 217, 217)
End With

Set oChtObj = Nothing

Dim oPoint                      As Excel.Point
Dim sngPercente                 As Single
With ser
    For n = 1 To .Points.Count
        Set oPoint = .Points(n)
        sngPercente = .Values(n) * 100

        With oPoint
            If sngPercente < 70 Then
                .Interior.Color = RGB(255, 0, 0)
            End If
            If sngPercente > 75 Then
                .Interior.Color = RGB(0, 176, 80)
            End If
            If sngPercente >= 70 And sngPercente <= 75 Then
                .Interior.Color = RGB(148, 208, 80)
            End If
            If sngPercente = 0 Then
                .DataLabel.Caption = "OFF"
            End If
        End With

    Next n
End With

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