Results 1 to 5 of 5
  1. #1
    animalscience
    Guest

    Sizing charts with VBA? (VBA / Excel 2000)

    Does anyone know how to get / set pixel dimensions for charts in excel?
    I've been trying to resize charts with the macro recorder, but it only lets me scale them by a percentage, I need to set their dimensions exactly...
    NEED HELP FAST

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sizing charts with VBA? (VBA / Excel 2000)

    ActiveChart.PlotArea.Height and ActiveChart.PlotArea.Width. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    animalscience
    Guest

    Re: Sizing charts with VBA? (VBA / Excel 2000)

    Thanks Sam, but I should have been more specific, I'm trying to resize the Chart Area to height and width in pixels, and the Macro Recorder does this:
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.02, msoFalse, msoScaleFromTopLeft
    Is it even possible to input the Chart Area in pixels? (it must be, right?)
    Thanks ahead of time...

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sizing charts with VBA? (VBA / Excel 2000)

    What do you mean by pixels? Excel's idea of pixels totally blows my mind! The height & width properties are in points, but you have the set of PointsToScreenPixels_ functions, so if you insist on pixels, this macro will resize the first chart to 300x300 pixels.
    <pre>Sub S300x300()
    With ActiveSheet.ChartObjects(1).ShapeRange
    .Height = .Height * 300 / ActiveWindow.PointsToScreenPixelsY(.Height)
    .Width = .Width * 300 / ActiveWindow.PointsToScreenPixelsX(.Width)
    End With
    End Sub</pre>

    Sorry about my earlier post of PlotArea, turns out that it's height & width properties are read-only. But, back to pixels, run a test case with my macro, first have cell A1 selected. Now, redraw the chart, select the chart, and run the macro. Cute! Evidently, worksheet pixels are not the same as embedded chart pixels, hence my original question!

    If you are drawing these charts in VBA, then you should use the Add method of the ChartObjects collection which allows you to specify the left, top, width, and height, all in points. Now you avoid all of the resize problems. Hope this helps! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sizing charts with VBA? (VBA / Excel 2000)

    > With ActiveSheet.ChartObjects(1).ShapeRange

    Thank You!

Posting Permissions

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