Excel 2003/XP (soon to be Excel 2007/Vista :(

Situation: Have a workbook with lots of shapes on various worksheets
- all of which need to run essentially the same macro when they are
clicked - it could be the exact same macro, if there was some way for
the macro code to know which shape called it.

The blindingly obvious creation of a separate macro for each shape is
more than I can expect the end-users to know how (or remember) to do;
a sad situation, but factual.

Application.Caller doesn't work for shapes and I'm running out of
good ideas to try. Since clicking on a shape doesn't select it, I
can't use the .Selected property...

Is this one of those "Well, who would want to do that?" things that MS
either decided to ignore or never thought of - or has someone cracked
this particular nut?

Any help most gratefully accepted!

James

Hi James,

Application.Caller doesn't work for shapes

It does !

in the macro assigned to the shape's On Action property -

Dim sCaller As String
sCaller = Application.Caller
MsgBox sCaller

' Select Case sCaller
'' etc

Regards,
Peter T

wrote in message
oups.com...

Excel 2003/XP (soon to be Excel 2007/Vista :(

Situation: Have a workbook with lots of shapes on various worksheets
- all of which need to run essentially the same macro when they are
clicked - it could be the exact same macro, if there was some way for
the macro code to know which shape called it.

The blindingly obvious creation of a separate macro for each shape is
more than I can expect the end-users to know how (or remember) to do;
a sad situation, but factual.

Application.Caller doesn't work for shapes and I'm running out of
good ideas to try. Since clicking on a shape doesn't select it, I
can't use the .Selected property...

Is this one of those "Well, who would want to do that?" things that MS
either decided to ignore or never thought of - or has someone cracked
this particular nut?

Any help most gratefully accepted!

James

James,
"Application.Caller doesn't work for shapes "

In my version of Excel "Application.Caller" returns the name of the shape
that called the macro... "AutoShape 1"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



wrote in message
Excel 2003/XP (soon to be Excel 2007/Vista :(
Situation: Have a workbook with lots of shapes on various worksheets
- all of which need to run essentially the same macro when they are
clicked - it could be the exact same macro, if there was some way for
the macro code to know which shape called it.

The blindingly obvious creation of a separate macro for each shape is
more than I can expect the end-users to know how (or remember) to do;
a sad situation, but factual.

Application.Caller doesn't work for shapes and I'm running out of
good ideas to try. Since clicking on a shape doesn't select it, I
can't use the .Selected property...

Is this one of those "Well, who would want to do that?" things that MS
either decided to ignore or never thought of - or has someone cracked
this particular nut?

Any help most gratefully accepted!

James

Application.Caller doesn't work for shapes and I'm running out of

This is only working for Forms controls

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...

Excel 2003/XP (soon to be Excel 2007/Vista :(

Situation: Have a workbook with lots of shapes on various worksheets
- all of which need to run essentially the same macro when they are
clicked - it could be the exact same macro, if there was some way for
the macro code to know which shape called it.

The blindingly obvious creation of a separate macro for each shape is
more than I can expect the end-users to know how (or remember) to do;
a sad situation, but factual.

Application.Caller doesn't work for shapes and I'm running out of
good ideas to try. Since clicking on a shape doesn't select it, I
can't use the .Selected property...

Is this one of those "Well, who would want to do that?" things that MS
either decided to ignore or never thought of - or has someone cracked
this particular nut?

Any help most gratefully accepted!

James

"Ron de Bruin" wrote in message
Application.Caller doesn't work for shapes and I'm running out of

This is only working for Forms controls

Works with Shapes for me, eg Rectangle, not sure about XL2007

Regards,
Peter T

Sorry, I misread (I was thinking about buttons)


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Peter T" <peter_t@discussions wrote in message ...
"Ron de Bruin" wrote in message
Application.Caller doesn't work for shapes and I'm running out of

This is only working for Forms controls

Works with Shapes for me, eg Rectangle, not sure about XL2007

Regards,
Peter T