Creating resizing macro - select chart as object?

[ellinoz]

Hi,

I'm trying to create a macro in Excel so that I can click onto a chart and
resize it without having to select the "Select Objects" arrow in the drawing
toolbar (this is for very PC illterate folk). I recorded the macro and got
this text:

Sub Chart_two_third_width()
'
' Chart_two_third_width Macro
' Macro recorded 03/04/2008 by IT
'

'
ActiveSheet.Shapes("Chart 1").Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 178.5
Selection.ShapeRange.Width = 340.5
With Selection
.Placement = xlMove
.PrintObject = True
End With
End Sub

However, it doesn't work and I have a sneaking suspicion that I need to get
something in there that will select the chart as an object rather than a
chart.

Can anyone help at all? I'm (quite clearly) winging it with Visual Basic!

Many thanks,
ellinoz

 

[Andy Pope]

Hi,

How about this.

Sub Chart_two_third_width()
'
' Chart_two_third_width Macro
' Macro recorded 03/04/2008 by IT
'

'
With ActiveSheet.ChartObjects("Chart 1")
With .ShapeRange
.LockAspectRatio = msoFalse
.Width = .Width * 0.66
End With
.Placement = xlMove
.PrintObject = True
End With

End Sub

Cheers
Andy

 

[ellinoz]

Hi Andy,

That's great thanks. But is there a way to make the changes to the selected
chart rather than whichever chart happens to be Chart1 on a sheet? Sorry -
my recorded macro was a bit misleading in that respect!

Thanks again,
El

 

[Andy Pope]

Here is a more generic version of the routine.

Sub Chart_two_third_width()
'
' Chart_two_third_width Macro
' Macro recorded 03/04/2008 by IT
'
If ActiveChart Is Nothing Then Exit Sub
'
With ActiveChart.Parent
With .ShapeRange
.LockAspectRatio = msoFalse
.Width = .Width * 0.66
End With
.Placement = xlMove
.PrintObject = True
End With

End Sub

Cheers
Andy