I have a "fill in" form on the first worksheet that I want to write a macro
for that will allow the user to go the second worksheet, choose a value from
that second worksheet and have the macro automatically fill in the value on
the first worksheet. The macro that I have written will jump the user into
the second worksheet, but I can not get it to allow the user to choose the
value, copy it and
jump back to first worksheet and paste. I also can write a macro that will
jump to the second worksheet and then jump back to the first worksheet and
paste the cell that is already selected when the workbook opens. I just
can't get the macro to
allow the user to choose the desired value.
--
Lisa

Check out Application.InputBox, especially its last argument.

NickHK

"Lisa" ...
I have a "fill in" form on the first worksheet that I want to write a macro
for that will allow the user to go the second worksheet, choose a value
from
that second worksheet and have the macro automatically fill in the value
on
the first worksheet. The macro that I have written will jump the user
into
the second worksheet, but I can not get it to allow the user to choose the
value, copy it and
jump back to first worksheet and paste. I also can write a macro that
will
jump to the second worksheet and then jump back to the first worksheet and
paste the cell that is already selected when the workbook opens. I just
can't get the macro to
allow the user to choose the desired value.
--
Lisa

How many cells can the user choose from? Are they all in 1 column or
1 row? Are they in one contiguous block or scattered around the
worksheet?

On Jun 7, 10:44?am, Lisa wrote:
I have a "fill in" form on the first worksheet that I want to write a macro
for that will allow the user to go the second worksheet, choose a value from
that second worksheet and have the macro automatically fill in the value on
the first worksheet. The macro that I have written will jump the user into
the second worksheet, but I can not get it to allow the user to choose the
value, copy it and
jump back to first worksheet and paste. I also can write a macro that will
jump to the second worksheet and then jump back to the first worksheet and
paste the cell that is already selected when the workbook opens. I just
can't get the macro to
allow the user to choose the desired value.
--
Lisa

Lisa wrote:
I have a "fill in" form on the first worksheet that I want to write a macro
for that will allow the user to go the second worksheet, choose a value from
that second worksheet and have the macro automatically fill in the value on
the first worksheet. The macro that I have written will jump the user into
the second worksheet, but I can not get it to allow the user to choose the
value, copy it and
jump back to first worksheet and paste. I also can write a macro that will
jump to the second worksheet and then jump back to the first worksheet and
paste the cell that is already selected when the workbook opens. I just
can't get the macro to
allow the user to choose the desired value.

Since you're using a form, how about adding the "RefEdit"-control to the
form. This control mimics Excel's range-picking functionality.

If it is not on your Form Toolbox yet, rightclick on it, and select
"Additional Controls..." - Scroll down to the "RefEdit.Ctrl" and check
the box in front - finish with "OK".

See http://support.microsoft.com/kb/213776/ for more info.

HTH, CoRrRan