Selececting a cell/range onscreen through vba
 

rezafloyd wrote:
Dear experts,
Is there any way to select a cell or range 'onscreen' during running a
VBA code.
I mean program stop to promp the user to select a cell/range and then
continues the run.
Regards

Why does the user need top select the cell/range? Why can't the code do it?

Alan Beban

Selececting a cell/range onscreen through vba
 

Dear experts,
Is there any way to select a cell or range 'onscreen' during running a
VBA code.
I mean program stop to promp the user to select a cell/range and then
continues the run.
Regards

Selececting a cell/range onscreen through vba
 

rezafloyd wrote:
Dear experts,
Is there any way to select a cell or range 'onscreen' during running a
VBA code.
I mean program stop to promp the user to select a cell/range and then
continues the run.
Regards

Hi rezafloyd,

here is a trivial example of code that prompts the user to select a
range of cells for processing. If a new range of cells is not selected
when prompted the current selection is the default range that is
processed...

Public Sub ChosenCells()
Dim rngWhatCells As Range
On Error GoTo CANCELLED
Set rngWhatCells = Application.InputBox( _
Prompt:="Select Cells for processing.", _
Title:="What Cells?", _
Default:=Selection.Address, _
Type:=8)
rngWhatCells.Value = "I'm a chosen cell"
CANCELLED:
End Sub

Ken Johnson

Selececting a cell/range onscreen through vba
 

Hi Ken,
Thanks a lot
Reza

here is a trivial example of code that prompts the user to select a
range of cells for processing. If a new range of cells is not selected
when prompted the current selection is the default range that is
processed...

Public Sub ChosenCells()
Dim rngWhatCells As Range
On Error GoTo CANCELLED
Set rngWhatCells = Application.InputBox( _
Prompt:="Select Cells for processing.", _
Title:="What Cells?", _
Default:=Selection.Address, _
Type:=8)
rngWhatCells.Value = "I'm a chosen cell"
CANCELLED:
End Sub

Ken Johnson

Selececting a cell/range onscreen through vba
 

Dear Ken,
Thanks again. could you please let me know if theres a way to select
the range in another open work book. i.e. the code is in file w1.xls
and I want to select the range in file w2.xls.
Thank you very much.
Reza

Ken Johnson wrote:
here is a trivial example of code that prompts the user to select a
range of cells for processing. If a new range of cells is not selected
when prompted the current selection is the default range that is
processed...

Public Sub ChosenCells()
Dim rngWhatCells As Range
On Error GoTo CANCELLED
Set rngWhatCells = Application.InputBox( _
Prompt:="Select Cells for processing.", _
Title:="What Cells?", _
Default:=Selection.Address, _
Type:=8)
rngWhatCells.Value = "I'm a chosen cell"
CANCELLED:
End Sub

Ken Johnson

Selececting a cell/range onscreen through vba
 

Dear Alan,
Beacuse I want the user can select some data in another workbook.
And those data are not always same size and same position.
Regards,
Reza

Selececting a cell/range onscreen through vba
 

Hi Reza,

Since both workbooks are open, the easiest way is to use the Windows
collection to get to w2.xls.

The following code snippet (in a standard module in w1.xls) activates
w2.xls, prompts the user to select a range of cells, then places those
cell values into w1.xls starting at A1...

Public Sub ChosenCells()
Dim rngWhatCells As Range
Windows("w2.xls").Activate
On Error GoTo CANCELLED
Set rngWhatCells = Application.InputBox( _
Prompt:="Select Cells for processing.", _
Title:="What Cells?", _
Default:=Selection.Address, _
Type:=8)
Windows("w1.xls").Activate
Range(Cells(1, 1), Cells(rngWhatCells.Rows.Count, _
rngWhatCells.Columns.Count)).Value = rngWhatCells.Value
CANCELLED: Windows("w1.xls").Activate
End Sub

Ken