Handle cancellation of InputBox to select range

Question

I have the following piece of code:

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

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

I have tried to use a Variant variable type but I can't handle it. In case of cancelling, it returns False, meanwhile in case of selecting a range, it returns Range of InputBox.

How can I avoid this error?

Answer 1

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 If
End 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 = a
End Sub

And the function would look like:

Function testFunc(ByVal a As Variant) As Range
  If TypeOf a Is Range Then Set testFunc = a
End 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 If
End Sub

If you still have any questions, just ask ;)