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" 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)

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" 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)

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" wrote in message
...
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" 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)

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" wrote in message
...
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" 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)

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" wrote in message
...
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" wrote in message
...
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" 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)