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" <(E-Mail Removed)> wrote in
message news:7F7DA474-92E6-461B-BD9D-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message news:074D8F15-18EC-4A6B-87BA-(E-Mail Removed)...
>> >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 :-)
>>
>>
>>
Similar Threads
