How can I code charts to be drawn at a predefined size so that I don't have to resize the chart wizard output?

Aja

Hi Aja,

This routine will resize all the chartobjects on the activesheet.

Sub ResizeChartObjects()
Dim sngWidth As Single
Dim sngHeight As Single
Dim objCht As ChartObject

sngWidth = 100
sngHeight = 75
For Each objCht In ActiveSheet.ChartObjects
objCht.Width = sngWidth
objCht.Height = sngHeight
Next
End Sub

Is this what you meant?

Cheers
Andy

Aja wrote:
How can I code charts to be drawn at a predefined size so that I don't have to resize the chart wizard output?

Aja

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

And when you make a chart, don't use Charts.Add. This first adds a chart
sheet, then transfers the chart to a sheet. Use the
Sheet.ChartObjects.Add directly. This allows you (forces you actually)
to include the position and size of the chart.

This is how it looks:

Sub AddChartObject()
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.ChartType = xlXYScatterLines
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
End With
End Sub

For more hints on charting with VBA, check out this web page:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Andy Pope wrote:

Hi Aja,

This routine will resize all the chartobjects on the activesheet.

Sub ResizeChartObjects()
Dim sngWidth As Single
Dim sngHeight As Single
Dim objCht As ChartObject

sngWidth = 100
sngHeight = 75
For Each objCht In ActiveSheet.ChartObjects
objCht.Width = sngWidth
objCht.Height = sngHeight
Next
End Sub

Is this what you meant?

Cheers
Andy

Aja wrote:

How can I code charts to be drawn at a predefined size so that I don't
have to resize the chart wizard output?
Aja

With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)

Just a quick word of warning about using the above method.
If the worksheet's zoom setting is not at 100%, then the position
and size of the resulting chartobject may not be exactly what you
specified. Fortunately, setting the chartobject's Top, Left, Width
& Height properties explicitly works properly at all zoom settings.

Regards,
Vic Eldridge


"Jon Peltier" wrote in message
...
And when you make a chart, don't use Charts.Add. This first adds a chart
sheet, then transfers the chart to a sheet. Use the
Sheet.ChartObjects.Add directly. This allows you (forces you actually)
to include the position and size of the chart.

This is how it looks:

Sub AddChartObject()
With ActiveSheet.ChartObjects.Add _
(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.ChartType = xlXYScatterLines
.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
End With
End Sub

For more hints on charting with VBA, check out this web page:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Andy Pope wrote:

Hi Aja,

This routine will resize all the chartobjects on the activesheet.

Sub ResizeChartObjects()
Dim sngWidth As Single
Dim sngHeight As Single
Dim objCht As ChartObject

sngWidth = 100
sngHeight = 75
For Each objCht In ActiveSheet.ChartObjects
objCht.Width = sngWidth
objCht.Height = sngHeight
Next
End Sub

Is this what you meant?

Cheers
Andy

Aja wrote:

How can I code charts to be drawn at a predefined size so that I don't
have to resize the chart wizard output?
Aja