I have a workbook with a "fill in form" on the first worksheet. In
one cell, I would like the user to be able to look at infomation on
the second worksheet, pick a value from that second worksheet
and have the value they select automatically input on the first
worksheet in that particular cell. I have written a macro that will
jump to the second worksheet, but after that I do not know how
to have the macro "pause" so the user can select a cell on the
second worksheet in order for the macro to paste it back on the
first worksheet. I have written another macro that will jump to
the second worksheet, copy a "preselected" cell, copy and paste
it back on the first worksheet in the correct cell....I just can get
the macro to "pause" and let the user select a cell.
--
Lisa

"Lisa" schreef in bericht
...
I have a workbook with a "fill in form" on the first worksheet. In
one cell, I would like the user to be able to look at infomation on
the second worksheet, pick a value from that second worksheet
and have the value they select automatically input on the first
worksheet in that particular cell. I have written a macro that will
jump to the second worksheet, but after that I do not know how
to have the macro "pause" so the user can select a cell on the
second worksheet in order for the macro to paste it back on the
first worksheet. I have written another macro that will jump to
the second worksheet, copy a "preselected" cell, copy and paste
it back on the first worksheet in the correct cell....I just can get
the macro to "pause" and let the user select a cell.
--
Lisa


Lisa, I think your problem is solved when you add a RefEdit to your
UserForm.

You could also use application.inputbox and ask for a range.

dim myRng as range

'your existing code here

set myrng = nothing
on error resume next
set myrng = application.inputbox(Prompt:="Select a range", type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel, what happens?
else
'something else here?
end if



Lisa wrote:

I have a workbook with a "fill in form" on the first worksheet. In
one cell, I would like the user to be able to look at infomation on
the second worksheet, pick a value from that second worksheet
and have the value they select automatically input on the first
worksheet in that particular cell. I have written a macro that will
jump to the second worksheet, but after that I do not know how
to have the macro "pause" so the user can select a cell on the
second worksheet in order for the macro to paste it back on the
first worksheet. I have written another macro that will jump to
the second worksheet, copy a "preselected" cell, copy and paste
it back on the first worksheet in the correct cell....I just can get
the macro to "pause" and let the user select a cell.
--
Lisa

--

Dave Peterson