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

John McGraw

Board Regular
Joined
Feb 25, 2002
Messages
76
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top