+ Reply to Thread
Results 1 to 3 of 3

Place chart at specific cell location

  1. #1
    Landmine
    Guest

    Place chart at specific cell location

    I would like to find a method to place a chart at a specific cell location
    within a worksheet. I am adding a chart using data within the spreadsheet
    and need to place the chart next to the data. I can't seem to find a method.

    The code I am using to add the sheet is as follows and I would like to place
    the top left hand corner in cell J2.

    Thanks
    BLandmine

    sName = ActiveSheet.Name
    Set sh = ActiveSheet
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=sh.Range("B3:C12"), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=sName
    ActiveChart.HasLegend = False

  2. #2
    sebastienm
    Guest

    RE: Place chart at specific cell location

    Hi
    When the chart is in a Worksheet you have to move the Chart container
    (PArent) which is a ChartObject object. So, say the chart is the #1 on the
    active sheet:
    '---------------------------------------
    Sub test()
    With ActiveSheet
    .ChartObjects(1).Top = .Range("B2").Top
    .ChartObjects(1).Left = .Range("B2").Left
    End With
    End Sub
    '---------------------------------------

    Now based on the ActiveChart, you would do something like
    '-----------------------------------------------------
    Sub test2()
    With ActiveChart.Parent 'ie With ChartObject
    .Top = .PArent.Range("B2").Top ' ie ActiveChart.Parent.Parent=Worksheet
    .Left = .PArent.Range("B2").Left
    End With
    End Sub
    --
    Regards,
    Sébastien
    <http://www.ondemandanalysis.com>


    "Landmine" wrote:

    > I would like to find a method to place a chart at a specific cell location
    > within a worksheet. I am adding a chart using data within the spreadsheet
    > and need to place the chart next to the data. I can't seem to find a method.
    >
    > The code I am using to add the sheet is as follows and I would like to place
    > the top left hand corner in cell J2.
    >
    > Thanks
    > BLandmine
    >
    > sName = ActiveSheet.Name
    > Set sh = ActiveSheet
    > Charts.Add
    > ActiveChart.ChartType = xlColumnClustered
    > ActiveChart.SetSourceData Source:=sh.Range("B3:C12"), _
    > PlotBy:=xlColumns
    > ActiveChart.Location Where:=xlLocationAsObject, Name:=sName
    > ActiveChart.HasLegend = False


  3. #3
    Vic Eldridge
    Guest

    RE: Place chart at specific cell location

    You've used Charts.Add to create the chart (most probably because that's the
    code that the macro recorder spits out). If you use ChartObjects.Add
    instead, you get the opportunity to specify the chartobject's size and
    position as you create it.

    eg.

    With ActiveSheet.ChartObjects.Add(Left:=Range("J2").Left, _
    Top:=Range("J2").Top, _
    Width:=Range("J2:M12").Width, _
    Height:=Range("J2:M12").Height).Chart
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Range("B3:C12"), _
    PlotBy:=xlColumns
    .HasLegend = False
    End With


    One thing to be aware of with the ChartObjects.Add method, is that when the
    window's zoom setting is not at 100% , the placement and size of the
    chartobject can be a little imprecise. Explicitly setting the chartobject's
    Top, Left, Width & Height properties (as demonstrated in Sébastien's reply)
    does not suffer from this problem.


    Regards,
    Vic Eldridge



    "Landmine" wrote:

    > I would like to find a method to place a chart at a specific cell location
    > within a worksheet. I am adding a chart using data within the spreadsheet
    > and need to place the chart next to the data. I can't seem to find a method.
    >
    > The code I am using to add the sheet is as follows and I would like to place
    > the top left hand corner in cell J2.
    >
    > Thanks
    > BLandmine
    >
    > sName = ActiveSheet.Name
    > Set sh = ActiveSheet
    > Charts.Add
    > ActiveChart.ChartType = xlColumnClustered
    > ActiveChart.SetSourceData Source:=sh.Range("B3:C12"), _
    > PlotBy:=xlColumns
    > ActiveChart.Location Where:=xlLocationAsObject, Name:=sName
    > ActiveChart.HasLegend = False


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1