[Harlan Grove[_5_]]

"Vasant Nanavati" wrote...
You need something like the following awkward construction:

Sub ForceRangeInput()
Dim vResp As Variant
Do Until TypeName(vResp) = "Range"
On Error Resume Next
Set vResp = Application.InputBox("Test", Type:=8)
On Error GoTo 0
Loop
vResp.EntireRow.Select
End Sub

It's tricky only because the InputBox can return two types, an object and a
boolean.
...

A variation: encapsulate the awkwardness in a function called by the procedure.

Sub foo()
Dim rng As Range
Set rng = InputRange(Prompt:="Select a range", Title:="foo", Force:=True)
If Not rng Is Nothing Then rng.EntireRow.Select
End Sub

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

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something else.

[Vasant Nanavati[_2_]]

Nicely constructed, Harlan. :-)

Regards,

Vasant.

"Harlan Grove" wrote in message
...
"Vasant Nanavati" wrote...
You need something like the following awkward construction:

Sub ForceRangeInput()
Dim vResp As Variant
Do Until TypeName(vResp) = "Range"
On Error Resume Next
Set vResp = Application.InputBox("Test", Type:=8)
On Error GoTo 0
Loop
vResp.EntireRow.Select
End Sub

It's tricky only because the InputBox can return two types, an object and
a
boolean.
..

A variation: encapsulate the awkwardness in a function called by the
procedure.

Sub foo()
Dim rng As Range
Set rng = InputRange(Prompt:="Select a range", Title:="foo", Force:=True)
If Not rng Is Nothing Then rng.EntireRow.Select
End Sub

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

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is
wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet.
There are
no add-ins or patches that increase them. Need more? Use something
else.