Within the same procedure, can such Inputbox be used more than onc

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)

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" <[email protected]> wrote in message
news:[email protected]...
> 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)

Dear Bob,

Thanks for helping but I can't understand.
Can you pls help to modify the sample. I'm a VBA rookie.

--
Edmund
(Using Excel XP)


"Bob Phillips" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
>
>
>

This is what I mean

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 = Nothing
Set UserRange = Application.InputBox _
(Prompt:="Show me which Item Number to work on?", _
Title:="Select Range", _
Type:=8)
If Not UserRange Is Nothing Then
UserRange.Select
Else
MsgBox "You cancelled the select"
End If
Exit Sub
Terminate:
End Sub


--
HTH

Bob Phillips

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

"Edmund" <[email protected]> wrote in message
news:[email protected]...
> Dear Bob,
>
> Thanks for helping but I can't understand.
> Can you pls help to modify the sample. I'm a VBA rookie.
>
> --
> Edmund
> (Using Excel XP)
>
>
> "Bob Phillips" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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)
> >
> >
> >

Dear Bob & Everyone,

I think I can guess why my codes are failing.

When the 1st Inputbox pops up, I select only a single cell. But when the 2nd
Inputbox pops up, I select a “range of cells”. I read F1 Help on inputbox
where it says "Type:=8" is "A cell reference, as a Range object". If I read
it correctly, procedure will fail/return error at my 2nd inputbox because I
select a range instead of a single cell.

If there's a better way to get around this constraint, pls share with me.
What I need is 3 inputbox popping up asking for user to specify which range
of cell or cells to work with. Atleast 1 of the 3 inputs will require the
selection of “range of cells” instead of just a single cell.

Thanks a lot.

--
Edmund
(Using Excel XP)


"Bob Phillips" wrote:

> This is what I mean
>
> 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 = Nothing
> Set UserRange = Application.InputBox _
> (Prompt:="Show me which Item Number to work on?", _
> Title:="Select Range", _
> Type:=8)
> If Not UserRange Is Nothing Then
> UserRange.Select
> Else
> MsgBox "You cancelled the select"
> End If
> Exit Sub
> Terminate:
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Edmund" <[email protected]> wrote in message
> news:[email protected]...
> > Dear Bob,
> >
> > Thanks for helping but I can't understand.
> > Can you pls help to modify the sample. I'm a VBA rookie.
> >
> > --
> > Edmund
> > (Using Excel XP)
> >
> >
> > "Bob Phillips" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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)
> > >
> > >
> > >
>
>
>

Edmund,

That is not your problem. Inputbox can handle a range no problem, just try
this on its own

Dim a
Set a = Application.InputBox("a", Type:=8)
MsgBox a.Address


--
HTH

Bob Phillips

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

"Edmund" <[email protected]> wrote in message
news:[email protected]...
> Dear Bob & Everyone,
>
> I think I can guess why my codes are failing.
>
> When the 1st Inputbox pops up, I select only a single cell. But when the
2nd
> Inputbox pops up, I select a "range of cells". I read F1 Help on inputbox
> where it says "Type:=8" is "A cell reference, as a Range object". If I
read
> it correctly, procedure will fail/return error at my 2nd inputbox because
I
> select a range instead of a single cell.
>
> If there's a better way to get around this constraint, pls share with me.
> What I need is 3 inputbox popping up asking for user to specify which
range
> of cell or cells to work with. Atleast 1 of the 3 inputs will require the
> selection of "range of cells" instead of just a single cell.
>
> Thanks a lot.
>
> --
> Edmund
> (Using Excel XP)
>
>
> "Bob Phillips" wrote:
>
> > This is what I mean
> >
> > 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 = Nothing
> > Set UserRange = Application.InputBox _
> > (Prompt:="Show me which Item Number to work on?", _
> > Title:="Select Range", _
> > Type:=8)
> > If Not UserRange Is Nothing Then
> > UserRange.Select
> > Else
> > MsgBox "You cancelled the select"
> > End If
> > Exit Sub
> > Terminate:
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Edmund" <[email protected]> wrote in message
> > news:[email protected]...
> > > Dear Bob,
> > >
> > > Thanks for helping but I can't understand.
> > > Can you pls help to modify the sample. I'm a VBA rookie.
> > >
> > > --
> > > Edmund
> > > (Using Excel XP)
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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)
> > > >
> > > >
> > > >
> >
> >
> >