T
Tony Rizzo
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
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