VBA ? - How to move a chart or shape based on a cell referen
VBA ? - How to move a chart or shape based on a cell referen
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA ? - How to move a chart or shape based on a cell referen

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     

    I would like to know if I can move and scale charts in VBA relative to a cell rather then pixel by pixel.

    I turn on the macro recorder and watched it do its thing while I move the chart, but it spits out stuff like ".incrementLeft 200.0" and ".IncrementTop 9#".

    I want to be able to write code to automaticaly move a chart based on cell values rather than these .increment values which look like pixel values. Is this possible? How would I do it?

    If an example would help to clarify, this is something I would like to do:

    - Move the top left hand corner of a chart
    to the top-left-hand corner of cell B3

    - Select the botom right corner of the
    chart and scale it to the bottom right
    hand corner of cell G7.

    This would give me a chart perfectly covering the range B3:G7.


    I hope that makes sense! Thanks much!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ActiveSheet.Shapes("Your Chart").Left = Sheet1.Cells(3, 2).Left
    ActiveSheet.Shapes("Your Chart").Top = Sheet1.Cells(3, 2).Top
    ActiveSheet.Shapes("Your Chart").Height = Sheet1.Range("G3:G7").Height
    ActiveSheet.Shapes("Your Chart").Width = Sheet1.Range("B7:G7").Width

    Tom

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom,

    Thats great man! Just what I needed. Thanks alot!


  4. #4
    New Member
    Join Date
    Jul 2009
    Location
    California
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA ? - How to move a chart or shape based on a cell referen

    This is a pretty old post that ended up being exactly what I was looking for. I hope it's not so old that it can't be resurrected.

    I have one small issue.

    If I include:
    ActiveSheet.Shapes("Your Chart").Left = Sheet1.Cells(3, 2).Left

    ...and run it (F5) within the body of the rest of a chart creator the movement is ignored and the chart remains in the location where it was created. If I step (F8) through the code at the above line it works. It gets worse. If I include a break point on that line and hold down F5 as it goes through the code it pauses just long enough at the break point for it to move it before it goes on. It only doesn't work if it's left to run on it's own.

    So the code is correct and excel is doing what it's expected to do when I step into each line item, but when left it's own devices it does not seem to be completing the request.

    Has anyone else seen this behavior and is there a way to ensure that the code performs the way it should?

    I'm using Excel2003 if that matters.

    Thanks.

  5. #5
    New Member
    Join Date
    Nov 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA ? - How to move a chart or shape based on a cell referen

      
    I'm having trouble with the code Tom provided. I've recorded a graph through a macro and placing it on another sheet. However, when I move the graph as I recorded the macro the "new position" wouldn't update. When I ran the macro on the other sheet the charts would pop in the same place every time. Therefore I was looking for a code to change the location of the charts and found this one. However, now when i implement the code i get a run-time error '424' object required... please see my script below and thank you for any help you can provide:

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$G$9:$H$16")
    ActiveChart.ChartType = xlPie
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    'ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.ChartTitle.Select
    'ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.ChartTitle.Text = "Fall 2009"
    ActiveChart.ChartArea.Select
    ActiveSheet.Shapes("Chart 3").Left = Sheet1.Cells(3, 2).Left
    ActiveSheet.Shapes("Chart 3").Top = Sheet1.Cells(3, 2).Top
    ActiveSheet.Shapes("Chart 3").Height = Sheet1.Range("G3:G7").Height
    ActiveSheet.Shapes("Chart 3").Width = Sheet1.Range("B7:G7").Width
    ActiveSheet.ChartObjects("Chart 3").Activate

    *the blue is the debug part which is causing the problem.... thanks again!
    Last edited by Agutier; Nov 7th, 2012 at 11:27 AM.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com