On Sun, 11 Feb 2007 22:13:27 -0500, "Jon Peltier"
wrote:


"Fredrik E. Nilsen" wrote in message
.. .
Hi,

If I have understood it correctly, there is no way to control the
placement of the Axis title object in a chart or control the text line
breaks in it through VB.

You can easily get or set the position of an axis title:

Sub AxisTitlePlacement()
If ActiveChart.HasAxis(xlValue, xlPrimary) Then
If ActiveChart.Axes(xlValue, xlPrimary).HasTitle Then
With ActiveChart.Axes(xlValue, xlPrimary).AxisTitle
' get axis title position
MsgBox "Axis Position Left: " & .Left & ", Top: " & .Top
' set axis title position
.Left = 0
.Top = ActiveChart.PlotArea.Height / 2 +
ActiveChart.PlotArea.InsideTop - 12
End With
End If
End If
End Sub

Ah, thank you. I was mislead by a couple of blogs, and I couldn't find
any documentation about it in the help-file.

Line breaks are not as straightforward. You cannot make the line any longer
than Excel decides (other than by shrinking the text or stretching the
chart), but you could insert hard breaks. You would have to write code smart
enough to read the text

MyText = ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text

and decide where a break should be.

Text boxes are in fact the way to get around the limitation you have in
resizing the title box.

The problem is that they don't want a line break for the axis-title at
all so I guess text boxes is the only way to go.

This procedure looks for a textbox with a particular name. If it finds it,
it updates the text. If it doesn't find it, it adds a new one, names it, and
adds text.

Sub UpdateOrAddTextBox()
Dim shp As Shape
Dim bTitleFound As Boolean
If ActiveChart.Shapes.Count 0 Then
For Each shp In ActiveChart.Shapes
If shp.Name = "textbox_YAxisTitle" Then
' we found it
bTitleFound = True
' adjust text and position of textbox
shp.TextFrame.Characters.Text = "New Y Axis Title"
End If
Next
End If
If Not bTitleFound Then
' didn't find it, so add it
Set shp = ActiveChart.Shapes.AddTextbox(msoTextOrientationUp ward, 0, _
ActiveChart.PlotArea.Height / 2 + ActiveChart.PlotArea.InsideTop -
12, 20, 100)
With shp
.Name = "textbox_YAxisTitle"
.TextFrame.Characters.Text = "Y Axis Title"
.TextFrame.AutoSize = True
End With
End If
End Sub

Fantastic, I will try this in the morning! Now it's 5 am local time, I
can barely read what you have written...:)

Thanks again!

--
Fredrik E. Nilsen