OnAction calls to subroutine
 

VBA - Office97 - Excel

I have a routine which populates a worksheet with shapes (rectangles). Each
is uniquely identified with an index number appended to its name. At he same
time, the "onaction" property in set to call a common subroutine.

This subroutine needs to know which shape was clicked, but the calling
routine (i.e. the onaction statement) does not seem to support arguments.
Does anybody know how I can identify the shape in the target subroutine after
the action is triggered.

Please note that it is impractical to call a different subroutine for every
shape on the sheet - there are too many of them!

Any assistance would be appreciated.

OnAction calls to subroutine
 

Application.Caller

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TerryP" wrote in message
...
VBA - Office97 - Excel

I have a routine which populates a worksheet with shapes (rectangles).
Each
is uniquely identified with an index number appended to its name. At he
same
time, the "onaction" property in set to call a common subroutine.

This subroutine needs to know which shape was clicked, but the calling
routine (i.e. the onaction statement) does not seem to support arguments.
Does anybody know how I can identify the shape in the target subroutine
after
the action is triggered.

Please note that it is impractical to call a different subroutine for
every
shape on the sheet - there are too many of them!

Any assistance would be appreciated.

OnAction calls to subroutine
 

Hi Terry,
you can use application.caller like that:

Sub ShapesInit()
Dim i%
With Worksheets(1)
For i = 1 To .Shapes.Count
..Shapes(i).OnAction = "Test"
Next i
End With
End Sub

Sub Test()
Select Case Application.Caller
Case "Rectangle 1"

Case "Rectangle 3"

'....
End Select
End Sub

Regards,
MP

"TerryP" a écrit dans le message de
...
VBA - Office97 - Excel

I have a routine which populates a worksheet with shapes (rectangles).
Each
is uniquely identified with an index number appended to its name. At he
same
time, the "onaction" property in set to call a common subroutine.

This subroutine needs to know which shape was clicked, but the calling
routine (i.e. the onaction statement) does not seem to support arguments.
Does anybody know how I can identify the shape in the target subroutine
after
the action is triggered.

Please note that it is impractical to call a different subroutine for
every
shape on the sheet - there are too many of them!

Any assistance would be appreciated.

OnAction calls to subroutine
 

application.Caller should return the name of the shape that triggered the
routine

--
Regards,
Tom Ogilvy

"TerryP" wrote in message
...
VBA - Office97 - Excel

I have a routine which populates a worksheet with shapes (rectangles).
Each
is uniquely identified with an index number appended to its name. At he
same
time, the "onaction" property in set to call a common subroutine.

This subroutine needs to know which shape was clicked, but the calling
routine (i.e. the onaction statement) does not seem to support arguments.
Does anybody know how I can identify the shape in the target subroutine
after
the action is triggered.

Please note that it is impractical to call a different subroutine for
every
shape on the sheet - there are too many of them!

Any assistance would be appreciated.

OnAction calls to subroutine
 

Great stuff! - works like a charm - thanks very much


"Michel Pierron" wrote:

Hi Terry,
you can use application.caller like that:

Sub ShapesInit()
Dim i%
With Worksheets(1)
For i = 1 To .Shapes.Count
..Shapes(i).OnAction = "Test"
Next i
End With
End Sub

Sub Test()
Select Case Application.Caller
Case "Rectangle 1"

Case "Rectangle 3"

'....
End Select
End Sub

Regards,
MP

"TerryP" a écrit dans le message de
...
VBA - Office97 - Excel

I have a routine which populates a worksheet with shapes (rectangles).
Each
is uniquely identified with an index number appended to its name. At he
same
time, the "onaction" property in set to call a common subroutine.

This subroutine needs to know which shape was clicked, but the calling
routine (i.e. the onaction statement) does not seem to support arguments.
Does anybody know how I can identify the shape in the target subroutine
after
the action is triggered.

Please note that it is impractical to call a different subroutine for
every
shape on the sheet - there are too many of them!

Any assistance would be appreciated.