Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inputbox type 64 (array) | Excel Discussion (Misc queries) | |||
InputBox / VBA question ok = print, cancel = exit | Excel Worksheet Functions | |||
Detecting Cancel in an InputBox Method | Excel Discussion (Misc queries) | |||
how do i cancel the type blocking in the cell? | New Users to Excel | |||
Inputbox method using type:=8 - How to Cancel? | Excel Programming |