Manipulating Data Labels for points on a chart

[Mr.Frog.to.you]

Hi Everyone,

I am building a macro to manage the labels on some data points on a
chart. In this macro I am able to work with each data point
individually, set the text and the position, the font, etc..... What I
am struggling with is determining the Y value (value) of the data
point that I am dealing with. I am attempting to check what the Y
value is via the parent object properties, and although I can see the
values in the locals viewer I cannot seem to refer to them in code.

eg/

for each p in c.SeriesCollection(i).Points
If p.Parent.Values(z) = 0 then.....

p is the point object
c is the chart (not chartobject)
i and z are counters (long), both initialised to 1 before use.

If anyone knows the correct syntax for accessing the values property
of the parent object I would be extremely grateful. The above attempt
gives me an error: (450) Wrong number of arguments or invalid property
assignment.

Cheers

The Frog

 

[Jim Cone]

Sub ChartTest()
Dim vArr As Variant
vArr = ActiveChart.SeriesCollection(1).Values
MsgBox vArr(1) & vbCr & vArr(UBound(vArr))
End Sub
'--

Also, this may be of interest...
http://spreadsheetpage.com/index.php/tip/a_class_module_to_manipulate_a_chart_series/

'--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html

..
..
..

"(e-mail address removed)"
wrote in message
Hi Everyone,

I am building a macro to manage the labels on some data points on a
chart. In this macro I am able to work with each data point
individually, set the text and the position, the font, etc..... What I
am struggling with is determining the Y value (value) of the data
point that I am dealing with. I am attempting to check what the Y
value is via the parent object properties, and although I can see the
values in the locals viewer I cannot seem to refer to them in code.

eg/

for each p in c.SeriesCollection(i).Points
If p.Parent.Values(z) = 0 then.....

p is the point object
c is the chart (not chartobject)
i and z are counters (long), both initialised to 1 before use.

If anyone knows the correct syntax for accessing the values property
of the parent object I would be extremely grateful. The above attempt
gives me an error: (450) Wrong number of arguments or invalid property
assignment.

Cheers

The Frog

 

[Mr.Frog.to.you]

Jim, thankyou for the feedback. It is exactly what I am looking for.
It was silly of me to ignore the fact I am already using the series
collection and can just refer to its values the same as I do the
points - they will be matched! <slaps self on forehead>

I looked at the class module actually before posting this thread. I
felt that it was a little heavy for what I am trying to achieve. It is
nice to know I am on the right track though. While I think of it, and
before I attempt it, perhaps you might know if there is a large
difference between the Excel chart objecct model and MS Graph - if I
wish to reproduce the functionality in other applications such as
Word, PowerPoint and Access?

Cheers

The Frog

 

[Jim Cone]

TF,
You are welcome.
I have never used MS Graph.
I don't know anything about it, but I doubt if MS Graph can be manipulated with VBA.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..

"(e-mail address removed)"
wrote in message
Jim, thankyou for the feedback. It is exactly what I am looking for.
It was silly of me to ignore the fact I am already using the series
collection and can just refer to its values the same as I do the
points - they will be matched! <slaps self on forehead>

I looked at the class module actually before posting this thread. I
felt that it was a little heavy for what I am trying to achieve. It is
nice to know I am on the right track though. While I think of it, and
before I attempt it, perhaps you might know if there is a large
difference between the Excel chart objecct model and MS Graph - if I
wish to reproduce the functionality in other applications such as
Word, PowerPoint and Access?
Cheers
The Frog

 

[Jon Peltier]

MS Graph can in fact be manipulated with VBA. However, it's a bit
cumbersome, and lacks some of the features.

In 2007 the Office programs started using Excel as their graphics
engine, which sounds good, but until SP2 it was impossible to access the
object models of Excel charts built in other applications.

- Jon