Determine if data label overlaps chart title?
 

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

Determine if data label overlaps chart title?
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
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

Determine if data label overlaps chart title?
 

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

Jon Peltier wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
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

Determine if data label overlaps chart title?
 

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 wrote:
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

Jon Peltier wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...

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




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Determine if data label overlaps chart title?
 

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

Andy Pope wrote:
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 wrote:
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

Jon Peltier wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...

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

Determine if data label overlaps chart title?
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
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

Andy Pope wrote:
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 wrote:
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

Jon Peltier wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...

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

Determine if data label overlaps chart title?
 

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 wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
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

Andy Pope wrote:
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 wrote:
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

Jon Peltier wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...

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

Determine if data label overlaps chart title?
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Andy Pope" wrote in message
...
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 wrote:
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

Jon Peltier wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...

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




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info