Cancel cancel InputBox for range selection

Question

I have the following code snippet:

dim selectRange as Range Set selectRange = Application.InputBox("Select your range", "Hello", , , , , , 8) 

When the user selects Cancel the InputBox prompt, it returns error of Object not set .

I tried to use the variable type Variant, but I can not handle it. In case of cancellation, it returns False , meanwhile, in the case of selecting a range, it returns the range of InputBox.

How to avoid this error?

Answer 1

This is a problem when choosing a range with an input field. Excel returns an error before the range returns, and it carries this error when you click cancel.

Therefore, you should actively handle this error. If you do not want something to happen when you click cancel, you can simply use the code as follows:

 Sub SetRange() Dim selectRange As Range On Error Resume Next Set selectRange = Application.InputBox("Select your range", "Hello", , , , , , 8) Err.Clear On Error GoTo 0 End Sub 

Answer 2

I'm late to the party here, but that was the only place I could find by explaining why I was having problems just checking my variable for no reason. As explained in the accepted answer, vbCancel in a range object is not treated the same as a string object. The error must be captured using the error handler.

I hate error handlers. So I divided it into my own function

 Private Function GetUserInputRange() As Range 'This is segregated because of how excel handles cancelling a range input Dim userAnswer As Range On Error GoTo inputerror Set userAnswer = Application.InputBox("Please select a single column to parse", "Column Parser", Type:=8) Set GetUserInputRange = userAnswer Exit Function inputerror: Set GetUserInputRange = Nothing End Function 

Now in my main section I can

 dim someRange as range set someRange = GetUserInputRange if someRange is Nothing Then Exit Sub 

In any case, this is not the same as the accepted answer, because it allows the user to handle this error only with the help of a special error handler and not need resume next , otherwise the rest of the procedure is handled the same way. If someone is here, like me.

Answer 3

While this question is a little older, I still want to show the correct way to do this without errors. You can do this either using a function or using sub.

Your basic procedure looks something like this:

 Sub test() Dim MyRange As Range testSub Application.InputBox("dada", , , , , , , 8), MyRange 'doing via Sub Set MyRange = testFunc(Application.InputBox("dada", , , , , , , 8)) ' doing via function If MyRange Is Nothing Then Debug.Print "The InputBox has been canceled." Else Debug.Print "The range " & MyRange.Address & " was selected." End If End Sub 

Sub (funny):

 Sub testSub(ByVal a As Variant, ByRef b As Range) If TypeOf a Is Range Then Set b = a End Sub 

And the function will look like this:

 Function testFunc(ByVal a As Variant) As Range If TypeOf a Is Range Then Set testFunc = a End Function 

Now just use the method you like and delete the unused line.

If you call a sub or function, you do not need the Set parameter. However, it does not matter whether the InputBox returns an object or not. All you have to do is check if this parameter is the object you want or not, and then act accordingly.

EDIT

Another smart way is to do the same behavior with a collection like this:

 Sub test() Dim MyRange As Range Dim MyCol As New Collection MyCol.Add Application.InputBox("dada", , , , , , , 8) If TypeOf MyCol(1) Is Range Then Set MyRange = MyCol(1) Set MyCol = New Collection If MyRange Is Nothing Then Debug.Print "The inputbox has been canceled" Else Debug.Print "the range " & MyRange.Address & " was selected" End If End Sub 

If you still have questions, just ask;)

Answer 4

I found that checking for the “Required Object” error you were talking about is one way to handle undo.

 On Error Resume Next dim selectRange as Range ' InputBox will prevent invalid ranges from being submitted when set to Type:=8. Set selectRange = Application.InputBox("Select your range", "Hello", , , , , , 8) ' Check for cancel: "Object required". If Err.Number = 424 Then ' Cancel. Exit Sub End If On Error GoTo 0