Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Execution | Excel Discussion (Misc queries) | |||
getting a simple macro to begin execution on the selected cell | Excel Discussion (Misc queries) | |||
automatic macro execution | Excel Programming | |||
Macro execution linked to cell entry | Excel Programming |