Changing order of embedded charts

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

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"
--
- K Dales


"Lee" wrote:

> 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

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

"K Dales" wrote:

> 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"
> --
> - K Dales
>
>
> "Lee" wrote:
>
> > 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

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.
--
- K Dales


"Lee" wrote:

> Yes, it is the index that I need to change, but, I will not be adding any
> additional charts to the worksheet.
> Regards,
> Lee
>
> "K Dales" wrote:
>
> > 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"
> > --
> > - K Dales
> >
> >
> > "Lee" wrote:
> >
> > > 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

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

"K Dales" wrote:

> 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.
> --
> - K Dales
>
>
> "Lee" wrote:
>
> > Yes, it is the index that I need to change, but, I will not be adding any
> > additional charts to the worksheet.
> > Regards,
> > Lee
> >
> > "K Dales" wrote:
> >
> > > 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"
> > > --
> > > - K Dales
> > >
> > >
> > > "Lee" wrote:
> > >
> > > > 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

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/
_______


Lee wrote:

> 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
>
> "K Dales" wrote:
>
>
>>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.
>>--
>>- K Dales
>>
>>
>>"Lee" wrote:
>>
>>
>>>Yes, it is the index that I need to change, but, I will not be adding any
>>>additional charts to the worksheet.
>>>Regards,
>>>Lee
>>>
>>>"K Dales" wrote:
>>>
>>>
>>>>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"
>>>>--
>>>>- K Dales
>>>>
>>>>
>>>>"Lee" wrote:
>>>>
>>>>
>>>>>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