View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default Within the same procedure, can such Inputbox be used more than onc

This works fine. Are you sure that the user is not Cancelling the InputBox?

The other things you might want to do is to null the range before calling
inputbox, as it still holds the value from the previous run, that is Set
UserRange = Nothing, and also you might want to remove the Default to the
InputBox, and remove the implicit option.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edmund" wrote in message
...
I use Sub SelectRange() to identify user range. But it keeps returning the
same range even though we get to select range for 3 separate times. If it

is
not possible to re-use Sub SelectRange() more than once within the same
procedure, then pls teach me a way to do so.

In summary, my main procedure needs 3 input from users(range is chosen by
users). If possible, pls allow users to select range using inputbox.

Thanks a lot.

Option Explicit
Dim UserRange As Range

Private Sub MainProc()
Call SelectRange
..[ instruction_1]
Call SelectRange
..[ instruction_2]
Call SelectRange
..[ instruction_3]
End Sub


Private Sub SelectRange()
Dim DefaultRange As String
DefaultRange = Selection.Address
On Error GoTo Terminate
Set UserRange = Application.InputBox _
(Prompt:="Show me which Item Number to work on?", _
Title:="Select Range", _
Default:=DefaultRange, _
Type:=8)
UserRange.Select
Exit Sub
Terminate:
End Sub


--
Edmund
(Using Excel XP)