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

[Edmund]

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

 

[Bob Phillips]

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]

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)

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)

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

 

[Bob Phillips]

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)

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)

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)

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]

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)

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)

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)

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)

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

 

[Bob Phillips]

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)

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)

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)

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)


:

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)

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