Select a cell in the middle of a macro execution
 

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.

Select a cell in the middle of a macro execution
 

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

--
Regards,
Tom Ogilvy

Dave Bash wrote in message
om...
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.

Select a cell in the middle of a macro execution
 

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


"Dave Bash" wrote in message
om...
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.

Select a cell in the middle of a macro execution
 

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

--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
Application.Msgbox or using refedit on a userform are about the only ways
within a macro.

--
Regards,
Tom Ogilvy

Dave Bash wrote in message
om...
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.

Select a cell in the middle of a macro execution
 

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


--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
Application.Msgbox or using refedit on a userform are about the only ways
within a macro.

--
Regards,
Tom Ogilvy

Dave Bash wrote in message
om...
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.