locate picture location using macro

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

Hi ME,.

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

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


---
Regards,
Norman


"ME @ Home" <r.mcc@ntlworld.com> wrote in message
news:E8A43FAE-7600-4A3B-B7B6-FBE8B8022D90@microsoft.com...
>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
>

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

In article <E8A43FAE-7600-4A3B-B7B6-FBE8B8022D90@microsoft.com>,
ME @ Home <r.mcc@ntlworld.com> wrote:

> 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

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

Hi ME,

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

---
Regards,
Norman



"ME @ Home" <r.mcc@ntlworld.com> wrote in message
news:CBC0DABA-4D46-4EC6-B755-F6566F9D9436@microsoft.com...
> 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

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




"JE McGimpsey" wrote:

> 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
>
> In article <E8A43FAE-7600-4A3B-B7B6-FBE8B8022D90@microsoft.com>,
> ME @ Home <r.mcc@ntlworld.com> wrote:
>
> > 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
>