gpt4 book ai didi

vba - 取消选择标签和图表Excel VBA

转载 作者:行者123 更新时间:2023-12-04 22:04:56 26 4
gpt4 key购买 nike

我正在使用“Sheet1”上的命令按钮使用 VBA 创建图表,但是该图表正在添加到另一个工作表(“Sheet2”)。
添加图表后,我使用以下代码根据 DataLabel 值对条形图进行着色并更改 DataLabel:

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”后,我注意到图表和其中的最后一个数据标签仍处于选中状态。
Chart
(来源: gulfup.com)
如何取消/取消选择此图表?
这是我尝试过的:
Worksheets("Sheet2").Range("A1").Select
由于代码是从另一张纸上运行的,因此不起作用。
ActiveChart.Deselect
根本不起作用。
删除 oPoint.DataLabel.Select从代码行。
不可能,因为没有它,代码将因运行时错误而失败。
SendKeys "{ESC}"
有效,但非常不可靠,就像与其他宏一起使用它会破坏代码,这将给出“代码执行已被中断”错误。
还有什么我可以尝试的吗?

最佳答案

我会通过阅读值而不是标题来完全避免这个问题:

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

关于vba - 取消选择标签和图表Excel VBA,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25326925/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com