Resizing a chart using VBA

[tmanger]

Hello all,

I would like to start by thanking everyone. I am a "lurker" and don't post often, but has provided some fantastic solutions for my VBA and Excel sheets!!! (As well as the realization that there is much I don't know about programming). Thank you all.

My current problem is changing the size of pie chart. I cannot seem to get the code to recognize the Shape Name, despite naming the chart. I am sure it is something simple that I am overlooking and appreciate any insight. This is the code I am trying to use:

Sub Resizechart()

Range("A1:B3").Select
Charts.Add
ActiveChart.Name = "Breakfast Chart"
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B3"), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlRight

ActiveSheet.Shapes("Chart 43").ScaleWidth 0.67, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 43").ScaleHeight 1.11, msoFalse, msoScaleFromTopLeft
ActiveWindow.Visible = False
Windows("MyOwnChartMaker.xls").Activate
Range("A6").Select

End Sub

It's finding a common, reproducible name for "Chart 43", since this changes each time I run the macro.

Many thanks.

Tom.

 

[Andrew Poulsom]

Is it?

ActiveSheet.Shapes(ActiveChart.Name).ScaleWidth 0.67, msoFalse, msoScaleFromTopLeft

 

[tmanger]

Yes. That is a better way to clarify.

The full macro actually makes the chart, copies it to a userform. Once the userform is closed, the active chart is deleted.

Tom.

 

[Andrew Poulsom]

Did you try it?

 

[tmanger]

I'm sorry, I thought you were speaking generically.

Yes I did try:

ActiveSheet.Shapes(ActiveChart.Name).ScaleWidth 0.67, msoFalse, msoScaleFromTopLeft

and received a runtime error "Item with specified name wasn't found"

I did try this:

ActiveSheet.Shapes(1).ScaleWidth 0.67, msoFalse, isoScaleFromTopLeft

and it works, as long as the chart is the only shape on the sheet. Just didn't know if there was a way to name the shapes with VBA other than using an indexing system.

Tom.

 

[Andrew Poulsom]

The Shape has the name of the ChartObject containing the Chart, so try:

ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 0.67, msoFalse, msoScaleFromTopLeft

 

[tmanger]

Excellent!! Sometimes the simple things escape the my simple mind.

Thank you.

Cheers,

Tom.