Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Sherbrooke, Qc, Canada, Quebec, Canada
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Positioning a chart in Excel from VBA (VBA 6 / Exc

    Hello,

    I made a VBA program to import and format data which comes from an AS400 computer and now I need to make a chart of this data.
    I need the graphic to be as an object of the same worksheet where the data is kept.
    I was wondering how to position and size the the graphic from within VBA code ex: I want to start my graphic in a certain position (x,y) which is on the same page as the data but "under" the data so both are seen at the same time (the data being in the top part of a page and the graphic being in the bottom part of the same page).

    I would greatly appreciate some ideas about this

    Thanks in advance

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

    Re: Positioning a chart in Excel from VBA (VBA 6 / Exc

    The charts on a worksheet form the ChartObjects collection, numbered 1, 2, ...

    A ChartObject has properties Top, Left, Height and Width. These are set in points (1 point = 1/72 inch).

    If you want to position a chart (assumed to be the first on the worksheet) 5 cm below the top of the worksheet, you can use

    ActiveSheet.ChartObjects(1).Top = CentimetersToPoints(5)

    If you want to postion and size the chart over a certain range of cells, you can use code like the following:

    Dim cht As ChartObject
    Dim rng As Range
    Set cht = ActiveSheet.ChartObjects(1)
    Set rng = ActiveSheet.Range("B10:H20")
    cht.Left = rng.Left
    cht.Top = rng.Top
    cht.Width = rng.Width
    cht.Height = rng.Height

    This example assumes:
    <UL><LI>The chart is on the active worksheet. If not, replace ActiveSheet by Worksheets("sheetname")
    <LI>The chart is the first on the worksheet. If not, change the index in ChartObjects(...)
    <LI>The chart should be positioned over B10:H20. Adapt this as needed.[/list]Regards,
    Hans

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Sherbrooke, Qc, Canada, Quebec, Canada
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Positioning a chart in Excel from VBA (VBA 6 /

    Thanks Hans,
    That's EXACTLY what I needed to know <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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