CODEWITHSUNDEEP
excelvba
shakeel ahmad
shakeel ahmad

Reputation: 73

How to handle Input Box Prompt Cancel Operation

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 areenter image description here 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. enter image description here

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

Upvotes: 0

Views: 669

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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.

Upvotes: 3

Related Questions