Urgent help! how to automatically resize all the charts?



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)

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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question