I know there are some great chart labelling tools out there, but I decided to
go it alone.... I can label a chart using a routine which reads a stats
report, and labels a,b or c or any combination of, depending on which groups
are different from control. Works great. But, I'm using xlLabelPositionAbove,
which sometimes plonks the labels on the error bars. I could use the
datalabel.top, but this is a bit rigid. How can I make the label position a
bit more dynamic so that it relates to the position of the point, and
possibly the size of the error bar. How do I ask excel where a point is on
the graph? thanks muchly :-)

Add an XY series to the chart, with X and Y selected so the plotted point is
at the top of the error bars (or another convenient location). Make the
series hidden (no marker and no lines) and assign custom labels to this
series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"foilfencingandy" wrote in
message ...
I know there are some great chart labelling tools out there, but I decided
to
go it alone.... I can label a chart using a routine which reads a stats
report, and labels a,b or c or any combination of, depending on which
groups
are different from control. Works great. But, I'm using
xlLabelPositionAbove,
which sometimes plonks the labels on the error bars. I could use the
datalabel.top, but this is a bit rigid. How can I make the label position
a
bit more dynamic so that it relates to the position of the point, and
possibly the size of the error bar. How do I ask excel where a point is on
the graph? thanks muchly :-)

Thanks Jon, good plan. I will use it as a simple option if I have to, though
I would still like to be able to interact with the chart data from within VBA
- is this possible? Can you query the position of a plotted point?
Main problem is that the graphs are generated within a template which I'd
rather not have to mess with too much, adding extra data series etc.

thanks for your time,
Andy

"Jon Peltier" wrote:

Add an XY series to the chart, with X and Y selected so the plotted point is
at the top of the error bars (or another convenient location). Make the
series hidden (no marker and no lines) and assign custom labels to this
series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"foilfencingandy" wrote in
message ...
I know there are some great chart labelling tools out there, but I decided
to
go it alone.... I can label a chart using a routine which reads a stats
report, and labels a,b or c or any combination of, depending on which
groups
are different from control. Works great. But, I'm using
xlLabelPositionAbove,
which sometimes plonks the labels on the error bars. I could use the
datalabel.top, but this is a bit rigid. How can I make the label position
a
bit more dynamic so that it relates to the position of the point, and
possibly the size of the error bar. How do I ask excel where a point is on
the graph? thanks muchly :-)

Andy -

You can of course interact with the chart elements via VBA. The problem with
data labels is that changing away from one of the standard positions to a
prescribed .Top and .Left break the positional link between a label and its
data point. If the data or axes change, the points all move but the label no
longer keeps up. Using a spare series ensures that the label stays with the
point you so cleverly determined the appropriate X and Y for.

In my experience, using spare series in templates is more robust and easier
for the developer than using VBA to make every little change. You can
determine positions of chart elements based on X and Y values if you're
comfortable with algebra, but if you use the helper series approach, you can
skip the hard math.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"foilfencingandy" wrote in
message ...
Thanks Jon, good plan. I will use it as a simple option if I have to,
though
I would still like to be able to interact with the chart data from within
VBA
- is this possible? Can you query the position of a plotted point?
Main problem is that the graphs are generated within a template which I'd
rather not have to mess with too much, adding extra data series etc.

thanks for your time,
Andy

"Jon Peltier" wrote:

Add an XY series to the chart, with X and Y selected so the plotted point
is
at the top of the error bars (or another convenient location). Make the
series hidden (no marker and no lines) and assign custom labels to this
series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"foilfencingandy" wrote in
message ...
I know there are some great chart labelling tools out there, but I
decided
to
go it alone.... I can label a chart using a routine which reads a stats
report, and labels a,b or c or any combination of, depending on which
groups
are different from control. Works great. But, I'm using
xlLabelPositionAbove,
which sometimes plonks the labels on the error bars. I could use the
datalabel.top, but this is a bit rigid. How can I make the label
position
a
bit more dynamic so that it relates to the position of the point, and
possibly the size of the error bar. How do I ask excel where a point is
on
the graph? thanks muchly :-)

I've just had a play, and the wisdom of your logic now becomes clear. I shall
wander off and amend my templates accordingly.......

thanks again,
Andy

"Jon Peltier" wrote:

Andy -

You can of course interact with the chart elements via VBA. The problem with
data labels is that changing away from one of the standard positions to a
prescribed .Top and .Left break the positional link between a label and its
data point. If the data or axes change, the points all move but the label no
longer keeps up. Using a spare series ensures that the label stays with the
point you so cleverly determined the appropriate X and Y for.

In my experience, using spare series in templates is more robust and easier
for the developer than using VBA to make every little change. You can
determine positions of chart elements based on X and Y values if you're
comfortable with algebra, but if you use the helper series approach, you can
skip the hard math.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"foilfencingandy" wrote in
message ...
Thanks Jon, good plan. I will use it as a simple option if I have to,
though
I would still like to be able to interact with the chart data from within
VBA
- is this possible? Can you query the position of a plotted point?
Main problem is that the graphs are generated within a template which I'd
rather not have to mess with too much, adding extra data series etc.

thanks for your time,
Andy

"Jon Peltier" wrote:

Add an XY series to the chart, with X and Y selected so the plotted point
is
at the top of the error bars (or another convenient location). Make the
series hidden (no marker and no lines) and assign custom labels to this
series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"foilfencingandy" wrote in
message ...
I know there are some great chart labelling tools out there, but I
decided
to
go it alone.... I can label a chart using a routine which reads a stats
report, and labels a,b or c or any combination of, depending on which
groups
are different from control. Works great. But, I'm using
xlLabelPositionAbove,
which sometimes plonks the labels on the error bars. I could use the
datalabel.top, but this is a bit rigid. How can I make the label
position
a
bit more dynamic so that it relates to the position of the point, and
possibly the size of the error bar. How do I ask excel where a point is
on
the graph? thanks muchly :-)