• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Excel 2002: How do I use VBA to set the area size of my chart to 600 width 300 height?

I just upgraded my Office 97 to 2002.  I can't seem to set the absolute width and height of my chart to a 600 pixel width and a 300 pixel height on the activesheet.  Does anybody have a technique for this?

Many thanks,
0
tcalbaz
Asked:
tcalbaz
  • 3
1 Solution
 
sebastienmCommented:
As for your previous question:
several ways...
1. you can go through the Shape object:
        ActiveSheet.Shapes("Chart 1").width = 600
        ActiveSheet.Shapes("Chart 1").height = 300
2. or the ChartObjects()
     activesheet.chartobjects(1).width= 600
     activesheet.chartobjects(1).height= 300

however, the units are in  points, not pixels.
You can transform points to pixels, by using the
   PointsToScreenPixelsX
   PointsToScreenPixelsY
functions of the Window object, eg:
       ActiveSheet.Shapes("Chart 1").Width = 600 / ActiveWindow.PointsToScreenPixelsX(1)
    ActiveSheet.Shapes("Chart 1").Top = 300 / ActiveWindow.PointsToScreenPixelsY(1)

or by applying the function of the Application.Windows(1).

Just make sure a window is there when you run it.

Regards,
Sebastien
0
 
sebastienmCommented:
ooops , i made a mistake above.
(also, it seems like the function behave weird with small number like 1),. so:

    ActiveSheet.Shapes("Chart 1").Width = 600 / ActiveWindow.PointsToScreenPixelsX(1000) * 1000
    ActiveSheet.Shapes("Chart 1").Height = 300 / ActiveWindow.PointsToScreenPixelsY(1000) * 1000

Sebastien
0
 
tcalbazAuthor Commented:
Well Done!
Thank You Sebastienm!

Ted
0
 
sebastienmCommented:
Thank you for the points, Ted.

Regards,
Sebastien
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now