how to center a square plot area in a square chart

[Guest]

hi all,

i have recorded a macro to produce a square chart containing a square plot.
i wish if it's at all possible to have the square plot centered within the
chart. is there a way to do this? below is the macro routine that i have
recorded in case there is a sub that can be inserted into the macros to
center the plot area within the chart area.

thank you,
jes


Sub squareGraph()
'
' squareGraph Macro
'

'
On Error GoTo notice

ActiveChart.ApplyCustomType ChartType:=xlUserDefined,
TypeName:="MyScatter"
ActiveChart.Parent.Width = 350
ActiveChart.Parent.Height = 350
ActiveChart.PlotArea.Select
Selection.Width = 250
Selection.Height = 250
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Exit Sub
notice: MsgBox ("You didn't pick a chart first")

End Sub

 

[Jon Peltier]

There are a few things you need to keep in mind. First, turn off the font
autoscaling. In your recorded code, the syntax is TextElement.AutoScaleFont
= True; change this to False. Otherwise, the font size will change as you
resize the plot area, causing the plot area to change more than you
intended. Next, keep in mind that the plot area is larger than the rectangle
defined by the axes. It also includes a small margin around this rectangle,
plus the axis ticks and labels. The dimensions of the inner rectangle itself
are read only: you want to change these dimensions, but can only do so by
changing those of the plot area. Remember to allow room for the axis titles
and the chart title.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

 

[Guest]

Thank you Jon. Once I have auto-scaling turned off, the resulting chart's
plot area is more acceptably centered.

jes