Determine if data label overlaps chart title?

[Kate]

Hello again, all you experts. I now have run into a new issue
when generating charts via VBA.

I add data labels only if the datapoint belongs to a particular
company. Sometimes, the data label
is on the last (and highest) point on a distribution curve. It
then runs into the title of the chart.

I've been trying to capture this in code but am at a loss. I
can't find any property of the data label
that tells me its top. I haven't even gotten to trying to
compare this with the bottom of the chart title!

What I want to do is change the data label's position to bottom
instead of top, if it overlaps the chart title.

Does anyone have a nifty way to do this in VBA?

Thanks in advance,
Kate

 

[Jon Peltier]

The data label has a property called .Top. Where is the chart title? Is it
above the plot area, or is it within the plot area? If the title is above
the plot area, you might compare the .Top property to the .Top or .InsideTop
property of the plot area, and if the label is higher, change its position
to under the point. Here's a sample macro you might be able to tailor to
your project:

Sub AdjustDataLabel()
Dim iPt As Long

With ActiveChart.SeriesCollection(1)
For iPt = 1 To .Points.Count
If .Points(iPt).HasDataLabel Then
If .Points(iPt).DataLabel.Top < ActiveChart.PlotArea.Top Then
.Points(iPt).DataLabel.Position = xlLabelPositionBelow
End If
End If
Next
End With

End Sub


- Jon

 

[Kate]

Jon, I always love your advice! However, the title is inside the
plot area, so the solution below
doesn't work.

If the title had a height property, I could solve this. Last
night I dreamed of a solution,
which I just attempted and it failed because there is no height
property to the title.

I need to be able to determine if the top of the label is > the
bottom of the title. Is there any way
to do this?

Thank you! -Kate

 

[Andy Pope]

Hi Kate,

There is a crude way of testing the height.
Chart items can not be move outside of the chart area, so using this
information you can attempt to force the chart title beyond the
chartarea height. The chart title will be positioned as low as possible
and you can take the difference between the new Top value and the chart
area height as the charttitle height. Same approach can be used for the
width and data labels.

Cheers
Andy

 

[Kate]

Thanks, Andy. That is a possible work-around. Too bad such a
useful bit of
info as height of an object isn't available as a standard object
property!

Regards,
Kate

 

[Jon Peltier]

We have been suggesting this to Microsoft for years, and while 2007 lacks
these size properties, we may yet see them in a future release of Excel.

- Jon

 

[Kate]

The final solution I applied was to compare the top of the label
to the top of the chart's
plot area. If it was less, I moved the label below the data
point. Not ideal, but better than
taking the chance of running into the title.

Thanks to Jon and Andy for their input.

-Kate

 

[Jon Peltier]

I use this so much that I've made a class module that encapsulates the
process of measuring the width and height of titles and labels.

- Jon