Message box

[geotso]

Hi,

I've got the following scenario:

a user runs a macro I've provided, which among others copies a range of
cells of the same row and pastes it in the row above. After that, the macro
displays a message box prompting him to type his name into one (always the
seventh) of the pasted cells.

Well, is there a way to display in the message box the equivalent name of
the cell for any macro's use?
That means, if the copied range is A10:K10, the pasted range will be A9:K9
so, the cell for user's name will be the G9, and the message box will show:
"Please, type your name in cell G9"

while, if the copied range is R4:AB4, the pasted range will be R3:AB3 so,
the cell for his name will be the X3, and the message box will show:
"Please, type your name in cell X3"

Thanks

 

[Bob Phillips]

It can be done, but to answer fully we would need to know how you do the
copy/paste. Here is a suggestion

If you use a range object to do the past, say oRng which points at A9, then
use

Msgbox "Please, type your name in cell " &
oRng.Offset(0,6).Address(False,False)

 

[Neil]

geotso,

Why not have an input box in your macro asking for a name and then paste
that name in the cell within your macro.
UserName = inputbox("Type your name!","Name Required")

Neil

 

[geotso]

Yes, it might work.
I'll try it tomorrow in the office and let you know.

Thanks Bob

It can be done, but to answer fully we would need to know how you do
the copy/paste. Here is a suggestion

If you use a range object to do the past, say oRng which points at
A9, then use

Msgbox "Please, type your name in cell " &
oRng.Offset(0,6).Address(False,False)

 

[Bob Phillips]

If it doesn't, post the code to help.

--

HTH

RP

Yes, it might work.
I'll try it tomorrow in the office and let you know.

Thanks Bob

It can be done, but to answer fully we would need to know how you do
the copy/paste. Here is a suggestion

If you use a range object to do the past, say oRng which points at
A9, then use

Msgbox "Please, type your name in cell " &
oRng.Offset(0,6).Address(False,False)