Which Shape Ran The Macro?

[jhcoxx]

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

 

[Peter T]

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

 

[Jim Cone]

James,
Re: "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)


<[email protected]>
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

 

[Ron de Bruin]

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

This is only working for Forms controls

 

[Peter T]

This is only working for Forms controls

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

Regards,
Peter T

 

[Ron de Bruin]

Sorry, I misread (I was thinking about buttons)