Getting value from a graph for VBA variable

Question

I am trying to add data label only to the largest piece of a pie chart using VBA.

When I try to run the following code i get an error message: 'Object doesnt support this property of method'

The error referes to the line:

sem = ActiveSheet.ChartObjects("REJT").Chart.SeriesCollection(1).Points(x).Value

Can anyone tell me what is the issue with this line? Is there an alternative way of getting value of a chart data point?

Dim krb As Long
Dim x As Long
Dim rr As Long
Dim sem As Long
Dim xmax As Long

Set pts = ActiveSheet.ChartObjects("REJT").Chart.SeriesCollection(1).Points
krb = pts.Count
x = 1
rr = 0

'While loop to find largest part of pie chart
While x < (krb + 1)
sem = ActiveSheet.ChartObjects("REJT").Chart.SeriesCollection(1).Points(x).Value
MsgBox (sem)
If sem > rr Then
rr = sem
xmax = x
End If

x = x + 1
Wend

'Add data label to the largest part of pie chart
ActiveSheet.ChartObjects("REJT").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(xmax).Select
ActiveChart.SeriesCollection(1).Points(xmax).ApplyDataLabels

Have you tried this Stackoverflow answer which is similar to your need. It will get you the Max value in the chart. You can just add to the code to assign the data label when it is found. — Adach1979
– Adach1979, Commented May 9, 2014 at 11:29

Automate This · Accepted Answer · 2014-05-09 14:52:23Z

1

Here is one way to get the maximum point value from your pie chart and apply data label to its position:

Sub pieChartMaxDataLabel()
    Dim cht As Chart
    Set cht = ActiveChart

    maxValue = WorksheetFunction.Max(cht.SeriesCollection(1).Values)
    maxPosition = WorksheetFunction.Match(maxValue, cht.SeriesCollection(1).Values, 0)
    cht.SeriesCollection(1).Points(maxPosition).ApplyDataLabels

    Debug.Print "Max Value is: "; maxValue; " At Position: "; maxPosition
End Sub

Tested with chart values that look like this:

enter image description here

Results:

enter image description here

edited May 9, 2014 at 14:52

answered May 9, 2014 at 13:56

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting value from a graph for VBA variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related