Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Resizing Charts (2000 SP3)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resizing Charts (2000 SP3)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resizing Charts (2000 SP3)

    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"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resizing Charts (2000 SP3)

    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?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resizing Charts (2000 SP3)

    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.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resizing Charts (2000 SP3)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Resizing Charts (2000 SP3)

    You can use something like

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

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resizing Charts (2000 SP3)

    Thats great.

    Thanks yet again Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •