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!

Sub Resize()
'assuming all the charts are in one worksheet
ChartNames = Array("Chart 1", "Chart 2", "Chart 3") 'Add all chart names
For Count = 0 To 2 ' change accdgly based on # of charts in array above
CN = ChartNames(Count)
ActiveSheet.ChartObjects(CN).Activate

'To change the actual size of the chart
ActiveSheet.Shapes(CN).ScaleWidth 0.88, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(CN).ScaleHeight 1.19, msoFalse, msoScaleFromTopLeft
ActiveWindow.Visible = False

'To change specifics on the chart itself you can add to the with selection
criteria below
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 9 ' you can select the font size if you want to change
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Next Count
End Sub


Or you could have macro, assign a short cut (for example Ctrl+Shift+C) that
you can trigger after selecting a chart for formatting individually.

Good luck!

"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!

Thank you very much for your help! I will try soon!