Here's a VBA problem with which I'm having a serious struggle. I have a
bunch of autoshapes within a window. These are not controls. They are
rectangles, just like the ones created manually from the Draw toolbar.
Ultimately the autoshapes will be created programmatically, and they all
will be linked to a single procedure via the OnAction property. As the user
clicks any of the autoshapes, I need that single procedure to detect which
autoshape got clicked and ran the procedure. So far I have been completely
unable to discover how to detect the clicked autoshape that calls the one
macro. The application surely has this information. After all, it runs the
macro. But I can't figure out how retrieve the information.

I cannot have each auto shape linked to its own unique procedure, since that
would require me to create and keep track of numerous additional procedures,
all created or destroyed programmatically along with their respective
autoshapes. It would be a disaster. So I really need to be able to detect
programmatically which autoshape the user clicks.

I thought that perhaps I could write a procedure that would detect the
clicked autoshape by comparing the cursor's position to the window area
occupied by every autoshape. Toward this end, one kind soul showed me how
to retrieve the position of the cursor's position in screen coordinates
(pixels). I had hoped that this would solve my problem. But I couldn't
figure out how to transform the screen coordinates to window coordinates
cleanly (from screen pixels to window points, with 0,0 at the top-left
corner of the usable area). I've been able to come up with little more
than a kluge of a solution. It works for a very restricted case, and even
that is guaranteed solely for my one computer. My kluge falls apart as soon
as the window is resized, and it also needs manual intervention from the
user initially. Arghh!

At this time, the best possible solution would let me detect the clicked
autoshape (not controls) programmatically. A good second-place solution
would let me transform the cursor's hardware coordinates (x,y screen pixels)
cleanly to window coordinates, programmatically, without user intervention,
and despite the many dynamic changes in the dimensions of the usable area of
the active window. Even when the window is at full size, changes in the
usable area are caused by changes in the Excel options, such as hiding or
showing the vertical and horizontal scroll bars, the worksheet tabs, the row
and column headers. All these changes shift the window's coordinate system,
relative to the screen coordinate system, making a clean and reliable
transformation of the cursor's coordinates impossible.

I've been struggling with this one for a very long time. A couple of
personal projects have even died for lack of a usable solution to this
problem. So I'd be very grateful if anyone could help.

Tony Rizzo

Tony,

Assign the following sub to a shape and see what you get...

Sub GetInShape()
MsgBox Application.Caller
End Sub

Regards,
Jim Cone
San Francisco, USA


"Tony Rizzo" wrote in message
...
Here's a VBA problem with which I'm having a serious struggle. I have a
bunch of autoshapes within a window. These are not controls. They are
rectangles, just like the ones created manually from the Draw toolbar.
Ultimately the autoshapes will be created programmatically, and they all
will be linked to a single procedure via the OnAction property. As the user
clicks any of the autoshapes, I need that single procedure to detect which
autoshape got clicked and ran the procedure. So far I have been completely
unable to discover how to detect the clicked autoshape that calls the one
macro. The application surely has this information. After all, it runs the
macro. But I can't figure out how retrieve the information.

I cannot have each auto shape linked to its own unique procedure, since that
would require me to create and keep track of numerous additional procedures,
all created or destroyed programmatically along with their respective
autoshapes. It would be a disaster. So I really need to be able to detect
programmatically which autoshape the user clicks.

I thought that perhaps I could write a procedure that would detect the
clicked autoshape by comparing the cursor's position to the window area
occupied by every autoshape. Toward this end, one kind soul showed me how
to retrieve the position of the cursor's position in screen coordinates
(pixels). I had hoped that this would solve my problem. But I couldn't
figure out how to transform the screen coordinates to window coordinates
cleanly (from screen pixels to window points, with 0,0 at the top-left
corner of the usable area). I've been able to come up with little more
than a kluge of a solution. It works for a very restricted case, and even
that is guaranteed solely for my one computer. My kluge falls apart as soon
as the window is resized, and it also needs manual intervention from the
user initially. Arghh!

At this time, the best possible solution would let me detect the clicked
autoshape (not controls) programmatically. A good second-place solution
would let me transform the cursor's hardware coordinates (x,y screen pixels)
cleanly to window coordinates, programmatically, without user intervention,
and despite the many dynamic changes in the dimensions of the usable area of
the active window. Even when the window is at full size, changes in the
usable area are caused by changes in the Excel options, such as hiding or
showing the vertical and horizontal scroll bars, the worksheet tabs, the row
and column headers. All these changes shift the window's coordinate system,
relative to the screen coordinate system, making a clean and reliable
transformation of the cursor's coordinates impossible.

I've been struggling with this one for a very long time. A couple of
personal projects have even died for lack of a usable solution to this
problem. So I'd be very grateful if anyone could help.

Tony Rizzo

Sub GetShapeName()
With ActiveSheet.Shapes(Application.Caller).TextFrame
.Characters.Text = "You clicked me !!!" & vbLf & _
"My name is " & Application.Caller
End With
End Sub

Regards,
Greg Wilson

"Tony Rizzo" wrote:

Here's a VBA problem with which I'm having a serious struggle. I have a
bunch of autoshapes within a window. These are not controls. They are
rectangles, just like the ones created manually from the Draw toolbar.
Ultimately the autoshapes will be created programmatically, and they all
will be linked to a single procedure via the OnAction property. As the user
clicks any of the autoshapes, I need that single procedure to detect which
autoshape got clicked and ran the procedure. So far I have been completely
unable to discover how to detect the clicked autoshape that calls the one
macro. The application surely has this information. After all, it runs the
macro. But I can't figure out how retrieve the information.

I cannot have each auto shape linked to its own unique procedure, since that
would require me to create and keep track of numerous additional procedures,
all created or destroyed programmatically along with their respective
autoshapes. It would be a disaster. So I really need to be able to detect
programmatically which autoshape the user clicks.

I thought that perhaps I could write a procedure that would detect the
clicked autoshape by comparing the cursor's position to the window area
occupied by every autoshape. Toward this end, one kind soul showed me how
to retrieve the position of the cursor's position in screen coordinates
(pixels). I had hoped that this would solve my problem. But I couldn't
figure out how to transform the screen coordinates to window coordinates
cleanly (from screen pixels to window points, with 0,0 at the top-left
corner of the usable area). I've been able to come up with little more
than a kluge of a solution. It works for a very restricted case, and even
that is guaranteed solely for my one computer. My kluge falls apart as soon
as the window is resized, and it also needs manual intervention from the
user initially. Arghh!

At this time, the best possible solution would let me detect the clicked
autoshape (not controls) programmatically. A good second-place solution
would let me transform the cursor's hardware coordinates (x,y screen pixels)
cleanly to window coordinates, programmatically, without user intervention,
and despite the many dynamic changes in the dimensions of the usable area of
the active window. Even when the window is at full size, changes in the
usable area are caused by changes in the Excel options, such as hiding or
showing the vertical and horizontal scroll bars, the worksheet tabs, the row
and column headers. All these changes shift the window's coordinate system,
relative to the screen coordinate system, making a clean and reliable
transformation of the cursor's coordinates impossible.

I've been struggling with this one for a very long time. A couple of
personal projects have even died for lack of a usable solution to this
problem. So I'd be very grateful if anyone could help.

Tony Rizzo

Outstanding! Thank you!!!!!

Tony


"Jim Cone" wrote in message
...
Tony,

Assign the following sub to a shape and see what you get...

Sub GetInShape()
MsgBox Application.Caller
End Sub

Regards,
Jim Cone
San Francisco, USA


"Tony Rizzo" wrote in message
...
Here's a VBA problem with which I'm having a serious struggle. I have a
bunch of autoshapes within a window. These are not controls. They are
rectangles, just like the ones created manually from the Draw toolbar.
Ultimately the autoshapes will be created programmatically, and they all
will be linked to a single procedure via the OnAction property. As the
user
clicks any of the autoshapes, I need that single procedure to detect
which
autoshape got clicked and ran the procedure. So far I have been
completely
unable to discover how to detect the clicked autoshape that calls the one
macro. The application surely has this information. After all, it runs
the
macro. But I can't figure out how retrieve the information.

I cannot have each auto shape linked to its own unique procedure, since
that
would require me to create and keep track of numerous additional
procedures,
all created or destroyed programmatically along with their respective
autoshapes. It would be a disaster. So I really need to be able to
detect
programmatically which autoshape the user clicks.

I thought that perhaps I could write a procedure that would detect the
clicked autoshape by comparing the cursor's position to the window area
occupied by every autoshape. Toward this end, one kind soul showed me
how
to retrieve the position of the cursor's position in screen coordinates
(pixels). I had hoped that this would solve my problem. But I couldn't
figure out how to transform the screen coordinates to window coordinates
cleanly (from screen pixels to window points, with 0,0 at the top-left
corner of the usable area). I've been able to come up with little more
than a kluge of a solution. It works for a very restricted case, and
even
that is guaranteed solely for my one computer. My kluge falls apart as
soon
as the window is resized, and it also needs manual intervention from the
user initially. Arghh!

At this time, the best possible solution would let me detect the clicked
autoshape (not controls) programmatically. A good second-place solution
would let me transform the cursor's hardware coordinates (x,y screen
pixels)
cleanly to window coordinates, programmatically, without user
intervention,
and despite the many dynamic changes in the dimensions of the usable area
of
the active window. Even when the window is at full size, changes in the
usable area are caused by changes in the Excel options, such as hiding or
showing the vertical and horizontal scroll bars, the worksheet tabs, the
row
and column headers. All these changes shift the window's coordinate
system,
relative to the screen coordinate system, making a clean and reliable
transformation of the cursor's coordinates impossible.

I've been struggling with this one for a very long time. A couple of
personal projects have even died for lack of a usable solution to this
problem. So I'd be very grateful if anyone could help.

Tony Rizzo

I am humbled. This is great. Where is this stuff catalogged? I wouldn't
have found it in three lifetimes.

Tony

"Greg Wilson" wrote in message
...
Sub GetShapeName()
With ActiveSheet.Shapes(Application.Caller).TextFrame
.Characters.Text = "You clicked me !!!" & vbLf & _
"My name is " & Application.Caller
End With
End Sub

Regards,
Greg Wilson

"Tony Rizzo" wrote:

Here's a VBA problem with which I'm having a serious struggle. I have a
bunch of autoshapes within a window. These are not controls. They are
rectangles, just like the ones created manually from the Draw toolbar.
Ultimately the autoshapes will be created programmatically, and they all
will be linked to a single procedure via the OnAction property. As the
user
clicks any of the autoshapes, I need that single procedure to detect
which
autoshape got clicked and ran the procedure. So far I have been
completely
unable to discover how to detect the clicked autoshape that calls the one
macro. The application surely has this information. After all, it runs
the
macro. But I can't figure out how retrieve the information.

I cannot have each auto shape linked to its own unique procedure, since
that
would require me to create and keep track of numerous additional
procedures,
all created or destroyed programmatically along with their respective
autoshapes. It would be a disaster. So I really need to be able to
detect
programmatically which autoshape the user clicks.

I thought that perhaps I could write a procedure that would detect the
clicked autoshape by comparing the cursor's position to the window area
occupied by every autoshape. Toward this end, one kind soul showed me
how
to retrieve the position of the cursor's position in screen coordinates
(pixels). I had hoped that this would solve my problem. But I couldn't
figure out how to transform the screen coordinates to window coordinates
cleanly (from screen pixels to window points, with 0,0 at the top-left
corner of the usable area). I've been able to come up with little more
than a kluge of a solution. It works for a very restricted case, and
even
that is guaranteed solely for my one computer. My kluge falls apart as
soon
as the window is resized, and it also needs manual intervention from the
user initially. Arghh!

At this time, the best possible solution would let me detect the clicked
autoshape (not controls) programmatically. A good second-place solution
would let me transform the cursor's hardware coordinates (x,y screen
pixels)
cleanly to window coordinates, programmatically, without user
intervention,
and despite the many dynamic changes in the dimensions of the usable area
of
the active window. Even when the window is at full size, changes in the
usable area are caused by changes in the Excel options, such as hiding or
showing the vertical and horizontal scroll bars, the worksheet tabs, the
row
and column headers. All these changes shift the window's coordinate
system,
relative to the screen coordinate system, making a clean and reliable
transformation of the cursor's coordinates impossible.

I've been struggling with this one for a very long time. A couple of
personal projects have even died for lack of a usable solution to this
problem. So I'd be very grateful if anyone could help.

Tony Rizzo

Obviously in the help files. A good start is looking in the object browser
and using the macro recorder. However, actually knowing to look for
application.Caller may take a book or asking in the newsgroup.

--
Regards,
Tom Ogilvy


"Tony Rizzo" wrote in message
...
I am humbled. This is great. Where is this stuff catalogged? I wouldn't
have found it in three lifetimes.

Tony

"Greg Wilson" wrote in message
...
Sub GetShapeName()
With ActiveSheet.Shapes(Application.Caller).TextFrame
.Characters.Text = "You clicked me !!!" & vbLf & _
"My name is " & Application.Caller
End With
End Sub

Regards,
Greg Wilson

"Tony Rizzo" wrote:

Here's a VBA problem with which I'm having a serious struggle. I have
a
bunch of autoshapes within a window. These are not controls. They are
rectangles, just like the ones created manually from the Draw toolbar.
Ultimately the autoshapes will be created programmatically, and they
all
will be linked to a single procedure via the OnAction property. As the
user
clicks any of the autoshapes, I need that single procedure to detect
which
autoshape got clicked and ran the procedure. So far I have been
completely
unable to discover how to detect the clicked autoshape that calls the
one
macro. The application surely has this information. After all, it
runs
the
macro. But I can't figure out how retrieve the information.

I cannot have each auto shape linked to its own unique procedure, since
that
would require me to create and keep track of numerous additional
procedures,
all created or destroyed programmatically along with their respective
autoshapes. It would be a disaster. So I really need to be able to
detect
programmatically which autoshape the user clicks.

I thought that perhaps I could write a procedure that would detect the
clicked autoshape by comparing the cursor's position to the window area
occupied by every autoshape. Toward this end, one kind soul showed me
how
to retrieve the position of the cursor's position in screen coordinates
(pixels). I had hoped that this would solve my problem. But I
couldn't
figure out how to transform the screen coordinates to window
coordinates
cleanly (from screen pixels to window points, with 0,0 at the top-left
corner of the usable area). I've been able to come up with little
more
than a kluge of a solution. It works for a very restricted case, and
even
that is guaranteed solely for my one computer. My kluge falls apart as
soon
as the window is resized, and it also needs manual intervention from
the
user initially. Arghh!

At this time, the best possible solution would let me detect the
clicked
autoshape (not controls) programmatically. A good second-place
solution
would let me transform the cursor's hardware coordinates (x,y screen
pixels)
cleanly to window coordinates, programmatically, without user
intervention,
and despite the many dynamic changes in the dimensions of the usable
area
of
the active window. Even when the window is at full size, changes in
the
usable area are caused by changes in the Excel options, such as hiding
or
showing the vertical and horizontal scroll bars, the worksheet tabs,
the
row
and column headers. All these changes shift the window's coordinate
system,
relative to the screen coordinate system, making a clean and reliable
transformation of the cursor's coordinates impossible.

I've been struggling with this one for a very long time. A couple of
personal projects have even died for lack of a usable solution to this
problem. So I'd be very grateful if anyone could help.

Tony Rizzo