How to handle Input Box Prompt Cancel Operation

Question

Hy, I am using an input box for copy range from one sheet and paste it to another sheet. I got an error when I press the cancel button on the prompt for range selection. the error was Error Code 424 "Object Required". So I want to handle this error with message box "You did not select the copy range." I have tried it in different ways but could not get the final solution. Below are my code and error message.

Sub ImportDatafromotherworksheet()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Set wkbCrntWorkBook = ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(Prompt:="Select All Data You Want to Copy.", Title:="Select All Data", Default:="A1", Type:=8)
            
            On Error GoTo 0
            If rngSourceRange Is Nothing Then
            Exit Sub
            End If
            
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(Prompt:="Select destination cell", Title:="Select Destination", Default:="A2", Type:=8)
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close False
           
        End If
    End With
End Sub

So the above error appeared when I do the following work.

When I click the cancel button without a selection of any range, the above error message appears. How to resolve this issue, guide me, please. Thanks

Pᴇʜ · Accepted Answer · 2020-08-11 08:22:06Z

3

If you press cancel the InputBox returns a boolean False. But only objects can use Set and a boolean is no obejct. Therefore you need an On Error Resume Next to turn error reporting off.

On Error Resume Next 'turns error reporting off until GoTo 0
Set rngSourceRange = Application.InputBox(Prompt:="Select All Data You Want to Copy.", Title:="Select All Data", Default:="A1", Type:=8)
On Error GoTo 0 'turns error reporting on again.

Now rngSourceRange will be Nothing if you pressed cancel.

edited Aug 11, 2020 at 8:22

answered Aug 10, 2020 at 15:59

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to handle Input Box Prompt Cancel Operation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related