PDA

View Full Version : Help:User interaction when code running



yurble_vn
07-06-2007, 10:15 AM
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!!!

xld
07-06-2007, 10:18 AM
Few problems there, how will you know when it has been input?

You can use Application.InputBox or a modal userform.

yurble_vn
07-06-2007, 11:06 AM
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..

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

mikerickson
07-06-2007, 08:20 PM
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
07-07-2007, 05:38 AM
How about using RefEdit on a userform.

johnske
07-07-2007, 06:24 AM
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. Range("A1,A7,B3:D7,G1:H10")here?

yurble_vn
07-08-2007, 08:55 AM
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

xld
07-08-2007, 09:03 AM
Use a RefEdit control on a userform as MD suggested.

lucas
07-08-2007, 09:59 AM
I agree....what could be easier than that?

johnske
07-08-2007, 02:35 PM
Example how-to:


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

yurble_vn
07-10-2007, 08:07 PM
Thanks, you all are so kind...

How about select sheets?

mikerickson
07-11-2007, 07:39 AM
Dim mySheet as Worksheet
Set mySheet = Application.InputBox("pick a cell on the sheet", type:=8).Parent