LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Possible to change chart title and label box widths? [email protected] Charts and Charting in Excel 6 December 18th 06 09:44 PM
What does the '[Group]' label in the Excel title bar mean? Leon Brown Excel Discussion (Misc queries) 4 August 30th 05 09:17 PM
Possible to add second data label to pie chart? jpwolf00 Charts and Charting in Excel 7 June 10th 05 02:34 PM
Resize chart data label donesquire Charts and Charting in Excel 2 June 9th 05 03:06 PM
Pasting Objects into Chart title and Axis title Sam Charts and Charting in Excel 1 June 6th 05 08:50 PM


All times are GMT +1. The time now is 10:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"