Selececting a cell/range onscreen through vba

  • Thread starter Thread starter Alan Beban
  • Start date Start date
A

Alan Beban

rezafloyd said:
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
Click to expand...
Why does the user need top select the cell/range? Why can't the code do it?

Alan Beban
 
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
 
rezafloyd said:
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
Click to expand...

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
 
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
 
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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
How to Select A Cell in VBA with a Variable Row Number? 3
Delete range names within a selection 13
Copy value from changing cells without VBA 1
Where am I going wrong? Cells reference to a range appears to havestopped working! 5
HELP!!! I can't set formula to cell 1
Concat a three column range into one cell Then Next... 2
Named Range maker code 9

Back
Top