Chart - Title Widths

[Guest]

Chart - Title Widths

Does anyone know how to find the width of a title/axis title?

 

[Andy Pope]

Hi,

The charttitle, along with the other labelling items in a chart, do not
expose the Width or Height properties.

Try this workaround. (Beware of word wrapping).

'------------------
Sub X
MsgBox GetChartTitleWidth(ActiveChart)
End Sub

Function GetChartTitleWidth(MyChart As Chart) As Single
Dim sngOrigLeft As Single
If MyChart.HasTitle Then
sngOrigLeft = MyChart.ChartTitle.Left
MyChart.ChartTitle.Left = MyChart.ChartArea.Left +
MyChart.ChartArea.Width
GetChartTitleWidth = (MyChart.ChartArea.Width -
MyChart.ChartArea.Left) - MyChart.ChartTitle.Left
MyChart.ChartTitle.Left = sngOrigLeft
End If
End Function
'------------------

It uses the fact that the chart title can not be more beyond the area of
the chart. The routine tries and then uses the gap between the edge and
the new Left position to determine the width. Should be close enough.

Obvioulsy the same technique can be applied to get the height

Chart - Title Widths

Does anyone know how to find the width of a title/axis title?

--

Cheers
Andy

http://www.andypope.info

 

[Jon Peltier]

You have to do this indirectly, since as you've learned, the charttitle
object exposes no width (or height) property to VBA. But if you move it
as far to the right as possible, then the width of the chart minus the
left position of the charttitle is equal to the charttitle width.

activechart.ChartTitle.left = activechart.ChartArea.width

ChartAreaWidth = activechart.ChartArea.width - _
activechart.ChartTitle.left

Some simple experiments with the legend, which does expose its width,
indicates that the width calculated above is about 3 points too large.

To center the chart title, use this:

activechart.ChartTitle.left = activechart.ChartArea.width
activechart.ChartTitle.left = activechart.ChartTitle.left/2

- Jon