Moving chart using 'IncrementLeft'

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi everyone,

I'm trying to write some code to move the position of a chart in a worksheet. The problem is that the chart has a strange nubmer assigned to it '24' and this increments everytime I delete it and run a macro to redraw it. Is there anyway to reference the chart generically so that I can modify its location automatically in the same macro, using the incrementleft statement?

I have tried 'activesheet.shapes.select' (or something similar - cannot remember the exact syntax I used) etc. but it didn't work.

Thanks,

vcoder
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
After you've created it, it is probably the ActiveChart....

Then you can rename it by using a line like

ActiveChart.Name = "More Sensible Name"


The other option is to use only 1 chart and rather than redrawing it simply change the inputs. Using dynamic ranges can be useful for this purpose.

Philip
 
Upvote 0
Hi Philip,

Many thanks for your suggestions. I'll try to change the name of the chart to something more sensible.

I'll also try to reuse the same chart and instead change the inputs to it - the series references - this will also speed up execution.

Best wishes,

vcoder
 
Upvote 0
You have to name the chartobject, which is the parent of the chart:

ActiveChart.Parent.Name = "Better Name"

Also, the newly created chart is probably the last shape in the Z order, so

ActiveSheet.Shapes(ActiveSheet.Shapes.Count) is probably also an appropriate option for referencing it, at least while it's still fresh.

You might find the .Top and .Left, .Width and .Height properties of a chartobject or shape better than .IncrementLeft and .IncrementTop, .ScaleHeight and .ScaleWidth methods for adjusting position and size of such objects.

In fact, when you create the chart, don't use Charts.Add, which first adds a chart sheet, that you have to move to its position on the worksheet. Instead use

Worksheets("My Sheet").ChartObjects.Add(Left, Top, Width, Height)

which creates the chartobject directly, and uses the position and size you specify, all in the one statement.
 
Upvote 0
Hi Jon,

Thank you very much indeed! This is very useful information. I wasn't aware that the operators 'left', 'top', 'width' and 'height' were more appropriate to use in my case.

Best regards,

vcoder
 
Upvote 0
Hi Jon,

I tried the code you suggested:
Code:
Worksheets("My Sheet").ChartObjects.Add(Left, Top, Width, Height)
(using numbers in place of Left, Top...etc.), but the debugger didn't like something in it. I had a look at the help files to see if I could figure out which part of the syntax was to blame, but to no avail.

The debugger is expecting an '=' sign somewhere in the statement.

vcoder
 
Upvote 0
Hi vcoder

Assign the newly created chart to a chartobject variable. You can then use it to assign the name.

Ex:
Code:
Dim ChtObj As ChartObject

Set ChtObj = Worksheets("My Sheet").ChartObjects.Add(20, 20, 100, 100)
ChtObj.Name = "My Chart"

Hope this helps
PGC
 
Upvote 0
As PGC points out, when used as a function (property), you need the parentheses around the arguments:
Code:
Set ChtObj = Worksheets("My Sheet").ChartObjects.Add(Left, Top, Width, Height)

but when you are using it as a sub (method) you omit the parentheses:
Code:
Worksheets("My Sheet").ChartObjects.Add Left, Top, Width, Height

PGC explains one very good reason for using the first syntax, and assigning a variable to the new chart object.
 
Upvote 0
Dear PGC and Jon,

Thank you very much indeed. I was missing the parentheses.

Best wishes,

vcoder
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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