Solved: How to determine the axis title width?

[oleval]

Hi All,

I have a code that re-styles the chart in such a way that it put a secondary value axis. I'd like to put a title to the axis. To do this in a stylish way I need to find out width of the axis title. Any idea on this regard?

Thank you.

Regards,
Alexander

[Andy Pope]

Hi,

Chart textual items do not have a Width or Height property.

It is possible to get a very close value though by exploiting a function of the chart objects which is text items can not be moved off of the chartarea.

So what you do is force the title textbox to a position beyond the width of the chart. The chart will automatically position the title as close to the edge as possible. So you can now calculate the width based on actual left position and the left position just off the chart.


Sub x()

With ActiveChart
With .Axes(xlValue, xlSecondary)
MsgBox "Width =" & GetChartTextWidth(.AxisTitle)
End With
End With

End Sub

Function GetChartTextWidth(Title As Object) As Single
'
Dim sngOrigLeft As Single

With Title
sngOrigLeft = .Left
.Left = .Parent.Parent.ChartArea.Width
GetChartTextWidth = .Parent.Parent.ChartArea.Width - .Left - .Parent.Parent.ChartArea.Left
.Left = sngOrigLeft
End With

End Function


And the same approach can be used for height.

[aljonco]

Hi,
in Excel2007 I had problems with Chart Axis label positions from Excel2000 legacy spreadsheets. The following can help to perfectly position the Axis Titles, Chart Titles etc. i.e. using the +10000 pushes the title to the extreme bottom or right. Halving the remaining distance positions it fine.


'VISTA FIX - FINAL TITLE POSITIONING***************************************
With ActiveSheet.ChartObjects("Chart 28").Chart

With .ChartTitle
.Top = 0
.Left = .Left + 10000
.Left = .Left / 2
End With

With .Axes(xlValue).AxisTitle
.Top = .Top + 10000
.Top = .Top / 2
.Left = 0
End With

With .Axes(xlValue, xlSecondary).AxisTitle
.Left = .Left + 10000
.Top = .Top + 10000
.Top = .Top / 2
End With

End With