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").
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
After running this code and going to "Sheet2", I notice that the chart and the last datalabel in it are still selected.
Chart http://im84.gulfup.com/6WoE7k.png
How do I un/de-select this chart?
This is what I have tried:
Worksheets("Sheet2").Range("A1").Select
Does not work as the code is being run from another sheet.
ActiveChart.Deselect
Does not work at all.
Removing the oPoint.DataLabel.Select line from the code.
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
One small inconsistancy here
Worksheets("Sheet2").Range("A1").Select
I notice that in your code the sheet is named "Sheet 2" with a space. Are you trying to select a cell in a sheet that doesn't exist?
