I have made lots of charts for the graphic designer. Now she said to me
my charts are too big when she copies those to illustrator (she has to
resize every one). So she asked me to resize all the charts to the size
she wants in excel. Now, my problem is how I can automatically change
the size of my charts. There are too many and I cannot work on
individual. The deadline is approaching soon. Please help me figure it
out! Thank you very much!

Hi,

If your charts are chart objects on worksheets then you could use the
following code to size (width/height only) all charts to be the same as
the active one.

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

sngWidth = ActiveChart.Parent.Width
sngHeight = ActiveChart.Parent.Height

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

This will not attempt to make plot areas or any other chart element the
same.

Another way is the click the selection arrow on the drawing toolbar and
then group select all the chartobjects. You can then format the objects
all at only. Again only in terms of width/height of actual chart.

Cheers
Andy


crossingmind wrote:
I have made lots of charts for the graphic designer. Now she said to me
my charts are too big when she copies those to illustrator (she has to
resize every one). So she asked me to resize all the charts to the size
she wants in excel. Now, my problem is how I can automatically change
the size of my charts. There are too many and I cannot work on
individual. The deadline is approaching soon. Please help me figure it
out! Thank you very much!


--

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

Thank you so much Andy. The latter way worked well. The first way I met
a bug on the line
sngWidth = ActiveChart.Parent.Width
Don't know how to debug it. Could you help more? Thanks a lot!

Did you have one of the charts selected?
The code will apply the width/height of the currently selected to chart
to all the others on the sheet.

crossingmind wrote:
Thank you so much Andy. The latter way worked well. The first way I met
a bug on the line
sngWidth = ActiveChart.Parent.Width
Don't know how to debug it. Could you help more? Thanks a lot!


--

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

Yes, I did select one chart for try, and then the bug window popped
out. Dunno why. Thanks a lot!

Me neither, I just tried my posted code in another multi chart worksheet
without incident. ???

crossingmind wrote:
Yes, I did select one chart for try, and then the bug window popped
out. Dunno why. Thanks a lot!


--

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