| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Possible to change chart title and label box widths? | Charts and Charting in Excel | |||
| What does the '[Group]' label in the Excel title bar mean? | Excel Discussion (Misc queries) | |||
| Possible to add second data label to pie chart? | Charts and Charting in Excel | |||
| Resize chart data label | Charts and Charting in Excel | |||
| Pasting Objects into Chart title and Axis title | Charts and Charting in Excel | |||
Linear Mode
