get reference to cell from mousedown event

[David]

I have several activeX image controls on a worksheet. When the user clicks on
an image I would like to return a reference to the worksheet cell underneath.
Unfortunately the "me" keyword does not return the name of the control,
otherwise I could use "me" and TopLeftCell. Maybe I could work with the x & y
co-ords returned by the MouesDown event?
Any Ideas?

 

[Tom Ogilvy]

An Image control will have a click event. Obviously, since you write the
code for the click event, you already know which control it belongs to. You
can then use that information to get the topleftcell.

Private Sub Image1_Click()
MsgBox Image1.TopLeftCell.Address
End Sub

 

[David]

Thanks Tom,
I will be using many images on the same worksheet. Each one will be over a
different cell. I would really like to not have to use the name of each image
control to determine which image control the user clicked on. I would like to
have the mouseDown event handling code for each image control to be the same,
minimising the scope for errors, if possible. I know that the mousedown event
procedure returns the screen x & Y co-ords. Could these be used in some way
to either reference the cell or the name of the image control?

 

[Tom Ogilvy]

Again, the mousedown event would be a separate event for each image control
and in each event, you would know the name of the image control. If you
wanted to have a common procedure to perform an action, you could call this
procedure from each separate mousedown event and pass in the respective
image control.

The X and Y coordinates on the mouse down event are relative to the top left
corner of the image control, so you would need to know the control to
utilize that information.

the only other alternative is to use a class module as illustrated by John
Walkenbach - in this case, you would have a reference to the image control
and could use the topleftcell directly.

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine


although for commandbutton on a userform, the technique is readily adaptable
to image controls on a worksheet.