Macro to resize charts in Excel 2010

[EMH]

Hi, I need to create two macros that will allow me to resize any chart I select in a 2010 workbook to either 11.9cm wide (AKA narrow chart) or 18.24cm (AKA wide chart) - both need to have a height of 6.84cm. I know virtually nothing about VBA but I know the macros I recorded are not working because I selected a chart at the time of recording and therefore the macros refers only to that chart (i.e. Chart 2) - however I want the macros to be useable no matter which chart I select. Hope this makes sense and I realy hope someone can help me - I have spent hrs trawling through forums to see if I could get an answer! Thanks in advance, Eve

 

[VoG]

Try posting your recorded code.

 

[EMH]

All that's in there for the narrow chart is:

ActiveSheet.Shapes("Chart 2").Height = 193.8897637795
ActiveSheet.Shapes("Chart 2").Width = 337.3228346457
End Sub

I don't know anything but I can't believe this is complicated - it seems that one should be able to substitute "Chart 2" with something that is generic i.e. it doesn't matter what the actual chart name/number is??

Thanks

 

[VoG]

Try like this - select the chart then run the macro

Sub NarrowChart()
ActiveChart.Parent.Height = 193.8897637795
ActiveChart.Parent.Width = 337.3228346457
End Sub

 

[EMH]

I have just been playing around with some code that I picked up from a forum somewhere which does seem to be doing what I want - HOORAY:

With ActiveChart.Parent
With .ShapeRange
.LockAspectRatio = msoFalse
.Width = 337.3228346457
.Height = 193.8897637795
End With
.Placement = xlMove
.PrintObject = True
End With

If you see any glaring issues with this please let me know, otherwise I will consider this problem solved ... for now : )

 

[EMH]

THANK YOU - that confirms what I found - appreciate it - I have spent hours on this! : )

 

[VoG]

This may be of interest http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html

 

[pgc01]

Hi

Doesn't add much but maybe easier to read

Sub NarrowChart()
ActiveChart.Parent.Height = Application.CentimetersToPoints(6.84)
ActiveChart.Parent.Width = Application.CentimetersToPoints(11.9)
End Sub

 

[EMH]

Thanks - that is very useful as I can just use the straight measurements in cm!

 

[EMH]

And thanks to re inserting code - newbie and it's 1am.... am off to bed happy!