how to center a square plot area in a square chart

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

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
_______

"xppuser" <[email protected]> wrote in message
news:[email protected]...
> 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

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

jes

"Jon Peltier" wrote:

> 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
> _______
>
> "xppuser" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>