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]