Help:User interaction when code running

[yurble_vn]

I'm getting stuck with this issues and need you all help:

I'm intending to write a sub, in which the code will ask user to chose some sheet, or range or ... anything in the work sheet. And it wait your user input before running.

InputBox does not support Range, sheets. And it even not allow us to select anything.

Is there anyway to do that??

Please help!!!

[Bob Phillips]

Few problems there, how will you know when it has been input?

You can use Application.InputBox or a modal userform.

[yurble_vn]

Have found the following code in google...
Just a small question. Is there anyway to select multicells in stead of range? this code just get range even if I choose multi cells, it will return a range with the same cells number that I chosen

I means, how can I choose multi cells, then get their value, and put the value to a new range have the same cells with the cells I chosen.

Thanks..

[vba]Function InputRange(Optional Prompt As String = "", Optional Title As String = "", _
Optional Force As Boolean = False) As Range
Dim retry As Boolean
On Error Resume Next
Do
Set InputRange = Application.InputBox(Prompt:=Prompt, Title:=Title, Type:=8)
If InputRange Is Nothing And Force And Not retry Then
retry = True
Prompt = "YOU MUST SELECT A RANGE!" & Chr(13) & Prompt
End If
Loop While InputRange Is Nothing And Force
On Error GoTo 0
End Function[/vba]

[mikerickson]

This shows some of the ways to deal with the range value returned by the Application.InputBox.

Sub test()
Dim xRange As Range
Dim inRange As Range

Set inRange = inputRange("test input"):Rem calls your function

MsgBox "You selected a range " & _
    inRange.Rows.Count & " rows by " & _
    inRange.Columns.Count & " columns."
    
For Each xRange In inRange
    MsgBox xRange.Value
Next xRange

With Range("a1")
    Range(.Cells(1, 1), .Cells(inRange.Rows.Count, inRange.Columns.Count)) = inRange.Value
End With

End Sub

[mdmackillop]

How about using RefEdit on a userform.

[johnske]

Have found the following code in google...
Just a small question. Is there anyway to select multicells in stead of range? this code just get range even if I choose multi cells, it will return a range with the same cells number that I chosen

I means, how can I choose multi cells, then get their value, and put the value to a new range have the same cells with the cells I chosen.

Thanks.. [/vba]

By multi-cells, do you mean to refer to non-contiguous ranges such as

A1, A7, B3:D7, and G1:H10

i.e. [VBA]Range("A1,A7,B37,G1:H10")[/VBA]here?

[yurble_vn]

By multi-cells, do you mean to refer to non-contiguous ranges such as

Yep, but how to get it as input for another function? Because the inputbox always return contiguous range

[Bob Phillips]

Use a RefEdit control on a userform as MD suggested.

[lucas]

I agree....what could be easier than that?

[johnske]

Example how-to:

[vba]
Sub a()
Dim SelectedRanges As Range
Set SelectedRanges = Application.InputBox("Select your ranges - use " & _
"commas to separate non-contiguous ranges", , "A1,B2:C9,F3,z11", , , , , 8)
SelectedRanges.Select
MsgBox SelectedRanges.Address
End Sub
[/vba]

[yurble_vn]

Thanks, you all are so kind...

How about select sheets?

[mikerickson]

Dim mySheet as Worksheet
Set mySheet = Application.InputBox("pick a cell on the sheet", type:=8).Parent