Query the User for Cell Location
 

I am writing a simple macro that takes some cell values that I have retrieved
from a separate worksheet and would like to place them in a new location in
another worksheet. Navigating between the worksheets is not a problem but I
am not sure how to query the user to select the destination cell(s) for the
copied values. I would like to do it via a standard mouse click rather than
typing them in a userinput box if possible. Suggestions?

Query the User for Cell Location
 

Hi Chris,

Chris wrote:
I am writing a simple macro that takes some cell values that I have
retrieved from a separate worksheet and would like to place them in a
new location in another worksheet. Navigating between the worksheets
is not a problem but I am not sure how to query the user to select
the destination cell(s) for the copied values. I would like to do it
via a standard mouse click rather than typing them in a userinput box
if possible. Suggestions?

You could use Application.InputBox with Type:=8 to do this:

Application.InputBox Prompt:="Please enter or select a " & _
"destination range.", Type:=8

You'll want to get the selected range back (while handling cancels/etc), so
it will probably look more like this:

Sub test()
Dim rngDest As Range

On Error Resume Next
Set rngDest = Application.InputBox(Prompt:= _
"Please enter or select a destination range.", _
Type:=8)
On Error GoTo 0

If Not rngDest Is Nothing Then
MsgBox "Selected: '" & rngDest.Address & "'."
Else
MsgBox "Cancelled"
End If

Set rngDest = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Query the User for Cell Location
 

Create a user form & add a RefEdit to it.

Create a function that shows the UserForm & returns the content of the RefEdit.



"Chris" wrote in message ...
I am writing a simple macro that takes some cell values that I have retrieved
from a separate worksheet and would like to place them in a new location in
another worksheet. Navigating between the worksheets is not a problem but I
am not sure how to query the user to select the destination cell(s) for the
copied values. I would like to do it via a standard mouse click rather than
typing them in a userinput box if possible. Suggestions?