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

[John McGraw]

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!

[Tom Schreiner]

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

[John McGraw]

Tom,

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

[namdev]

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.