Handle cancellation of InputBox to select range

excel vba error-handling inputbox
Jeremy Newton picture Jeremy Newton · Oct 26, 2013 · Viewed 21.3k times · Source

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

Netloh picture Netloh · Oct 26, 2013

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 0
End Sub

Related questions

Handle Error 9 when there is an Empty Array

I am writing a script that will loop through an Excel spreadsheet and find if there are duplicates of selected cells. If there are duplicates then the function will return an array of which rows are duplicates and create a …

Read More
VBA Excel File Open Prompt Cancel Error

So I am using a file open prompt to gather a filename. I then open this file in a background instance, parse information to a dictionary and close the file. This works fine. The code for this is: Application.FileDialog(…

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More