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. |
Inputbox method using type:=8 - How to Cancel?
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. |
All times are GMT +1. The time now is 10:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com