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

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(msoTextOrientationHo rizontal, 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.

--
Fredrik E. Nilsen