locate picture location using macro

[Guest]

i have a sheet which among other things contains 3 coloum of images, the idea
of the images is that once clicked they activate a macro which runs asking
the user 4 questions , the macro then pastes the data into a preset area (in
the same row as the button and then deletes the pic that was clicked)

at the min it looks like i have to write identical macros for each image ,
with only the position (row) numbers changing,,,, is their a macro \ control
function that can read the location of the picture

row a b c d e
name picture picture

row b is fixed data .... row c,d,e are where data will be entered by the
macro after the ? are ansered

 

[Norman Jones]

Hi ME,.

'=============>>
Public Sub Tester002()
Dim sAddress As String

sAddress = ActiveSheet.Shapes(Application.Caller). _
TopLeftCell.Address
MsgBox sAddress
End Sub
'<<=============

 

[JE McGimpsey]

Not sure exactly what you're doing, since b, d, and e are columns, not
rows, but you can fine the top left cell and bottom right cell of a
shape using the TopLeftCell and BottomRightCell properties:


Msgbox "Top Left Row: " & Pictures("Picture 1").TopLeftCell.Row

 

[Guest]

thanks for that

at the risk of being a pain , is their then a way of seperating the absoulte
refrence

why i ask is that the application caller address is $d$2 , but i then want
information to be pasted into c2 and d2 (at the min am using range("c2") .
range ("d2) ect


or if the callers address is $g$3 then the info to be entered into f3 & g3
ect

If MsgBox("Confirm " & Range("b2") & Chr$(13) & Chr$(13) & " Reg Number " &
Range("a2") & Chr$(13) & Chr$(13) & " With Trailer " & trailer & Chr$(13) &
Chr$(13) & " " & Time(), vbOKCancel, "Confirm Trunk Details For") = vbCancel
Then Exit Sub


thanks for your help

 

[Norman Jones]

Hi ME,

ActiveSheet.Shapes(Application.Caller). _
TopLeftCell.Address(0,0)

 

[Guest]

yah sorry wasnt well explained

i wanted to find the location address of a picture so that a macro could
enter data into the ROW Address of which pic was clicked

the pics are in various places,,, col d , col h ,col L
all three coloums have the same pic in from rows 2 to 30
at the click of any of these pics a msgbox appears asking question, i wanted
to fing the address of the picture that was clicked so the macro knew which
line to paste to results of the msgbox into


if i click on the pic in d26 , the results are recorded in c26 and in d26

if i click on the pic in h5 the results are displayed in g5 and h5

ect