Check for named shape in chart

[Fredrik E. Nilsen]

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.

I'm building a code to apply user defined settings on charts and the
code adds a textbox in stead of using the Axis title so the placement
can be controlled and the user can control the line breaks.

The problem is: I the user applies one user defined chart and then
change to another, the text box is added again. I'm sure there is a
way to check if a named text box exist in a chart, and add it if it
doesn't. This is the current code:

Sub Column()
If Not ActiveChart Is Nothing Then
With ActiveChart.Parent
.Height = 252.75
.Width = 342.75
End With
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Column"
ActiveChart.Legend.Left = 0
ActiveChart.Legend.Top = 250
ActiveChart.PlotArea.Left = 0
ActiveChart.PlotArea.Top = 25
ActiveChart.PlotArea.Height = 205
ActiveChart.PlotArea.Width = 340
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 2,
0, 0).Select
Selection.Characters.Text = "Y-axis title"
With Selection.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = xlAutomatic
.Background = xlTransparent
End With
With Selection
.AutoScaleFont = False
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
.Placement = xlMove
.PrintObject = True
.Name = "Y-axis title"
End With
Else
MsgBox "You have to select a chart before performing this
action.", _
vbExclamation, "No chart selected."
End If
End Sub

Any help would be great!

 

[Fredrik E. Nilsen]

On Mon, 12 Feb 2007 02:22:32 +0100, Fredrik E. Nilsen

I have been able to modify my code a bit to this:

Sub Column()
Dim shp1 As Shape
Dim shp2 As Shape
If Not ActiveChart Is Nothing Then
With ActiveChart.Parent
.Height = 252.75
.Width = 342.75
End With
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Column"
ActiveChart.Legend.Left = 0
ActiveChart.Legend.Top = 250
ActiveChart.PlotArea.Left = 0
ActiveChart.PlotArea.Top = 25
ActiveChart.PlotArea.Height = 205
ActiveChart.PlotArea.Width = 340
On Error Resume Next
Set shp1 = ActiveChart.Shapes("Y-axis title")
Set shp2 = ActiveChart.Shapes("X-axis title")
If shp1 Is Nothing Then
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 2,
0, 0).Select
Selection.Characters.Text = "Y-axis title"
With Selection.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = xlAutomatic
.Background = xlTransparent
End With
With Selection
.AutoScaleFont = False
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
.Placement = xlMove
.PrintObject = True
.Name = "Y-axis title"
End With
Else
ActiveChart.Shapes("X-axis title").Delete
End If
Else
MsgBox "You have to select a chart before performing this
action.", _
vbExclamation, "No chart selected."
End If
End Sub

The point is to remove the X-axis title if it exsist in the chart and
replace it with a Y-axis title. If a Y-axis title exist, nothing
should be done. If no title exist, a Y-axis title should be inserted.

This should of course be vice versa if the user applies a bar chart.

The current code works if you don't change from column to bar or
similar. If you change from column to bar, you have to run it twice to
remove the erronous title.

 

[Jon Peltier]

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

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.

I'm building a code to apply user defined settings on charts and the
code adds a textbox in stead of using the Axis title so the placement
can be controlled and the user can control the line breaks.

The problem is: I the user applies one user defined chart and then
change to another, the text box is added again. I'm sure there is a
way to check if a named text box exist in a chart, and add it if it
doesn't. This is the current code:

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(msoTextOrientationUpward, 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

- Jon

 

[Fredrik E. Nilsen]

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(msoTextOrientationUpward, 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!