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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
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
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354

ADVERTISEMENT

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
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Dear PGC and Jon,

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

Best wishes,

vcoder
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

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
Top