Changing order of embedded charts

[Guest]

I have six embedded charts on a worksheet and they are named Chart 2, Chart
10, Chart 12, Chart 13, Chart 14 and Chart 15. I would like to change the
order in this collection by renaming Chart 12 to Chart 3. Can anyone tell me
how to accomplish this task? I am using Excel 2003 running on Windows2000.
Thanks,
Lee

 

[Guest]

Do you need to change the index in the collection or do you just want the
name to be different. A collection index is a tricky thing as it is managed
internally by Excel and will potentially change as items are added or deleted
or moved. And changing the number in the name dees not change the index.
But changing the name would be easy enough:
Worksheets("SheetName").Chartobjects("Chart 12").Name = "Chart 3"

 

[Guest]

Yes, it is the index that I need to change, but, I will not be adding any
additional charts to the worksheet.
Regards,
Lee

 

[Guest]

The only thing I can even think to try would be to do this the way I reorder
arrays of variables when I need to:
- create a new (temporary) chartobject in code to hold copies of the
chartobjects as you move them around
- copy the chartobject you need to move into this temporary holding variable
- copy the destination chartobject into the one you just moved
- now copy the "temporary" chartobject into the item just vacated by the
chartobject that was moved.
I have never tried this and have no idea if it works or would be reliable;
but it is the only way I can think of doing what you want to do.

Other than that you might try searching all of MSDN for info on collections
and how they are indexed to see if there is info on how to reorder them.

 

[Guest]

Thank you for the input. I found a way around it by copying the embedded
charts in the order I would like them to be indexed and eliminated the
originals. I never thought it would be this difficult to change the order.
Regards,
Lee

 

[Jon Peltier]

If you change the Z order (i.e., send behind, send forward), the index
numbers change. 1 is the furthest back, 2 is in front of 1, 3 is in
front of 1 and 2, etc.

When you copied them in order, you actually were putting them into the
desired Z order.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______