Resizing Charts (2000 SP3)

Topic

New Reply

I want to make copies of existing charts, 6 charts to a page. At present I copy each chart in turn, paste it into the new sheet, then resize it to 5 columns wide and 20 rows high using the drag handle and holding down the Alt key. This is tedious as I have dozens of them and the project is ongoing. I have tried recording a macro, but this calls the chart by name, eg. ActiveSheet.Shapes(“Chart 1”), which is useless for subsequent charts, I have tried changing this to ActiveSheet.ActiveChart but the XL says ‘Object doesn’t support this property or method’. Can anyone help please? The macro should leave the top left corner of the chart where it is pasted and adjust the chart size to 5 columns by 20 rows, aligning with the sheet gridlines.

TIA

Reply | Quote

Replies

ActiveChart is a not property of the worksheet object (but of the Application, Window and Workbook objects). You can simply use ActiveChart without prefixing it with anything.

Reply | Quote

Thank you Hans.

Now VBA is saying “Method or data member not found” for the ScaleWidth

(the recorded macro was:
ActiveSheet.Shapes(“Chart 1”).ScaleWidth 0.74, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(“Chart 1”).ScaleHeight 0.49, msoFalse, msoScaleFromTopLeft

which has now become:

ActiveChart.ScaleWidth 0.74, msoFalse, msoScaleFromTopLeft
ActiveChart.ScaleHeight 0.49, msoFalse, msoScaleFromTopLeft

If I change this to:

ActiveChart.Shapes.ScaleWidth 0.74, msoFalse, msoScaleFromTopLeft
ActiveChart.Shapes.ScaleHeight 0.49, msoFalse, msoScaleFromTopLeft

VBA says “Object does not support this property or method”

Reply | Quote

You can use

ActiveChart.Parent.ShapeRange.ScaleWidth 0.74, msoFalse

(the argument msoScaleFromTopLeft is the default value, so you can safely omit it)

ActiveChart is a Chart, its Parent is a ChartObject, and the ShapeRange of that is the Shape that contains the chartobject (and hence the chart). Confusing, eh?

Reply | Quote

Thank you Hans

This is much better, the macro is now :

ActiveChart.Parent.ShapeRange.ScaleWidth 0.74, msoFalse
ActiveChart.Parent.ShapeRange.ScaleHeight 0.49, msoFalse

This works fine for the chart width, but the bottom of the chart is about 0.2 of a row height above the gridline at the bottom of row 20.

This may be significant, if I alter the column width the chart width increases with it, but if I alter the height of row 20 the chart does not move with it.

Reply | Quote

Playing around with the problem some more, I think the macro is working from the size of the original chart, which may not be identical for all my charts. Is it possible to specify the sizing to be 5 columns and 20 row, aligning with the gridlines in the new sheet.

Thanks.

Reply | Quote

You can use something like

ActiveChart.Parent.ShapeRange.Width = Range(“A1:E20”).Width
ActiveChart.Parent.ShapeRange.Height = Range(“A1:E20”).Height

Reply | Quote

Thats great.

Thanks yet again Hans

Reply | Quote