Select a cell in the middle of a macro execution

[Dave Bash]

I am using Excel 2k, writing code that activates 3 files, taking data
from the first, activating a second workbook, pasting via variant
array to a designated input range, assigning a designated output range
to a variant array. The next step is to return the results to the
original workbook, which is activated and similarly, data is brought
into a range, the sheet is calculated, and the output is put into a
variant for further processing.

My issue is this - on the 3rd and final workbook, I want to paste the
contents of the variant into a matrix of results ( ie, each time I run
the code, the target will change). I wanted to know if there is a way
- other than a msgbox where I designate the address - to click on a
cell and have the values be pasted into the active cell. As I type
this, I think that using a Preserve in a Dim statement may allow me to
actually let the code end and then run a new macro, off a double click
event perhaps. But I am looking for something more efficient.

Thanks for the assistance.

 

[Tom Ogilvy]

Application.Msgbox or using refedit on a userform are about the only ways
within a macro.

 

[Rob van Gelder]

Dave,

I'm curious as to how you are selecting values using MsgBox!

Here's a way using Application.InputBox

Sub test()
Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox("Select your Target", Type:=8)
If Err.Number Then Err.Clear
On Error GoTo 0

If Not rng Is Nothing Then ActiveCell.Value = rng.Cells(1).Value
End Sub

Rob

 

[Tom Ogilvy]

of course, Application.MsgBox should have been Application.Inputbox - but I
assume that is what you meant anyway.

 

[Tom Ogilvy]

Here's a way using Application.InputBox

Sub test1()
Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox("Select your Target", Type:=8)
On Error goto 0
If not rng is nothing then
rng.Select
End if

End Sub

 

[Dave Bash]

Thanks for the responses - the input box method and the ref edit are
both ways I have seen in the past. Wanted to know if there was
anything else I was missing.

I ended up using public variables in conjuction with redim Preserve.

in module 1:

Public clpBoard()

Sub persistentValue
erase clpBoard
sheets(1).activate
redim Preserve clpBoard(20,1)
clpBoard = Range("a1:a20")
End Sub

Sub retrieveAfterExecution
Range(ActiveCell.Address,ActiveCell.Offset(19,0).address) = clpBoard
End Sub

on Sheet 1:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
retrieveAfterExecution
End Sub


Thanks,

dB