Inputbox method using type:=8 - How to Cancel?
"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.
|