Chart Size

[Guest]

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]

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

 

[Jon Peltier]

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/ChartsHowTo/QuickChartVBA.html

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

 

[Vic Eldridge]

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