Handle cancellation of InputBox to select range

This is a problem when selection a range with an inputbox. Excel returns an error before the range is returned, and it carries this error on when you press cancel.

You should therefore actively handle this error. If you don't want anything to happen when you press cancel, you can just use the code like this:

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

While this question is a bit older I still want to show the proper way to do it without errors. You can do it either to it via function or with a sub.

Your main procedure is 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 IfEnd Sub

the Sub-way (funny) would be:

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

And the function would look like:

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

Now simply use the way you like and delete the unused line.

If calling a sub or a function you do not need to Set the parameter. That said, it doesn't matter if the InputBox returns an object or not. All you need to do, is to check if the parameter is the object you want or not and then act accordingly to it.

EDIT

Another smart way is using 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 IfEnd Sub

If you still have any questions, just ask ;)

I'm late to the party here but this was the only place I could find that explained why I was having trouble just checking my variable for nothing. As explained in the accepted answer, the vbCancel on a range object isn't handled the same way as a string object. The error must be caught with an error handler.

I hate error handlers. So I segregated it to its 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 Functioninputerror:    Set GetUserInputRange = NothingEnd Function

Now in my main sub I can

dim someRange as rangeset someRange = GetUserInputRangeif someRange is Nothing Then Exit Sub

Anyhow this is not the same as the accepted answer because it allows the user to only handle this error with a specific error handler and not need to resume next or have the rest of the procedure handled the same way. In case anyone ends up here like I did.