I desperately need to know which autoshape is clicked, programmatically

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
>
>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> I am humbled. This is great. Where is this stuff catalogged? I wouldn't
> have found it in three lifetimes.
>
> Tony
>
> "Greg Wilson" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >>
> >>
> >>
> >>
>
>