CODEWITHSUNDEEP
vbaexcel
CaptainABC
CaptainABC

Reputation: 1239

Reading Chart Label Values Runtime-Error (Excel VBA)

I have the below table for which I am creating a chart:

Table

After adding the chart I want to color the chart bars based on the label values and if the value of the label is 0 then the label should be changed to "OFF".

Below is my code to do so:

Dim ChartRng As Range

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
    .SeriesCollection(1).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)
    .SeriesCollection(1).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
    .SeriesCollection(1).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

For Each oPoint In Worksheets("Overview").ChartObjects("Performance").Chart.SeriesCollection(1).Points
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

For some reason I get the below error at the line sngPercente = CSng(Split(oPoint.DataLabel.Caption, "%")(0)):

Error

I have also tried using Split(oPoint.DataLabel.Text but was still getting an error.

It is noteworthy that the same code was running fine when viewed in Excel 2013, however it gives the above error in 2007.

Any help understanding the reason behind the error or possible workarounds will be highly appreciated.

Upvotes: 1

Views: 886

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6053

Your data label shows the Y value, right? Skip the data label and go straight to the values.

Dim vYVals As Variant
Dim srs As Series
Dim iPt as Long
Dim dPctg As Double ' NO ADVANTAGE TO SINGLE OVER DOUBLE

Set srs = ActiveChart.SeriesCollection(1)
vYVals = srs.Values

For iPt = 1 to srs.Points.Count
  dPctg = vYVals(iPt)

  With srs.Points(iPt)
    Select Case dPctg
      Case 0
        .DataLabel.Caption = "OFF"
      Case < 0.7
        .Interior.Color = RGB(255, 0, 0)
      Case > 0.75
        .Interior.Color = RGB(0, 176, 80)
      Case Else
        .Interior.Color = RGB(148, 208, 80)
    End Select
  End With
Next

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149277

I am not sure if Excel 2007 has Datalabel.Caption property as I cannot test it.

Try this

Add this line

Worksheets("Overview").ChartObjects("Performance").Chart.SeriesCollection(1).HasDataLabels = True

before the For Each oPoint In Worksheets("Overview")..... Loop and now try it.

If it still doesn't work then I will delete this post.

EDIT

As per THIS this property exists in Office 2007

Further testing on Teamviewer showed that in this version you have to select the Datalabel first before reading it's value. So all we had to do was add

oPoint.DataLabel.Select

before

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

and everything went smoothly.

Upvotes: 1

Related Questions