BringToFront syntax question

Hello,
I have 3 chartobjects "layered" on top of each other in a worksheet. Based
on a cell value, I want to "BringToFront" the correct chart. I have named
the charts by getting the first chart on top, holding the CTRL key while
clicking in the chart area, then change the name in the Cell indicator to the
left of the formula bar. I have named the charts Chart1, Chart2 & Chart 3,
this way in the order in which I want them to appear basis the cell number
(1,2,3).
In my code on the Worksheet("Graphs") code page, I cannot get the syntax
correct to "bringtofront" the correct sheet. I've tried everything I can get
my hands on....
activate, chart, chartobject, bringtofront, (1), (Chart1), ("Chart1"), etc...

I keep getting the "Application or Object defined error.
Please advise. I'm all ears.
Thanks,
Randy

Turn on the macro recorder (Tools | Macro > Record new macro...) then
use the GUI to bring any one object to the front (SHIFT+click or
CTRL+click an object then right-click and from the context menu select
Order > Bring to front.

Turn off the macro recorder and switch to the VBE to see what code XL
generated. You will find you have to access the ZOrder property of the
ShapeRange object associated with the chartobject.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Hello,
> I have 3 chartobjects "layered" on top of each other in a worksheet. Based
> on a cell value, I want to "BringToFront" the correct chart. I have named
> the charts by getting the first chart on top, holding the CTRL key while
> clicking in the chart area, then change the name in the Cell indicator to the
> left of the formula bar. I have named the charts Chart1, Chart2 & Chart 3,
> this way in the order in which I want them to appear basis the cell number
> (1,2,3).
> In my code on the Worksheet("Graphs") code page, I cannot get the syntax
> correct to "bringtofront" the correct sheet. I've tried everything I can get
> my hands on....
> activate, chart, chartobject, bringtofront, (1), (Chart1), ("Chart1"), etc...
>
> I keep getting the "Application or Object defined error.
> Please advise. I'm all ears.
> Thanks,
> Randy
>

Hint: Turn on the macro recorder, then manually perform the task you wish to
automate. That's how I learned this:

ActiveSheet.Shapes("Chart 1").ZOrder msoBringToFront

Other arguments for ZOrder are msoSendToBack, msoBringForward, and msoSendBackward.

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

RAP wrote:

> Hello,
> I have 3 chartobjects "layered" on top of each other in a worksheet. Based
> on a cell value, I want to "BringToFront" the correct chart. I have named
> the charts by getting the first chart on top, holding the CTRL key while
> clicking in the chart area, then change the name in the Cell indicator to the
> left of the formula bar. I have named the charts Chart1, Chart2 & Chart 3,
> this way in the order in which I want them to appear basis the cell number
> (1,2,3).
> In my code on the Worksheet("Graphs") code page, I cannot get the syntax
> correct to "bringtofront" the correct sheet. I've tried everything I can get
> my hands on....
> activate, chart, chartobject, bringtofront, (1), (Chart1), ("Chart1"), etc...
>
> I keep getting the "Application or Object defined error.
> Please advise. I'm all ears.
> Thanks,
> Randy