If user hits 'Cancel', workbook is closing

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
280
Office Version
  1. 365
Platform
  1. Windows
I have some vba to import a document and I call it using a button but I found if I select nothing and hit 'cancel', it's closing the entire workbook. How do I avoid this?

Code I'm calling:
VBA Code:
Sub ImportR()
 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim ws As Worksheet
   
   Set wkbCrntWorkBook = ActiveWorkbook
   
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Set ws = wkbSourceBook.Sheets(1)
         
        With Sheets(1)
            .Range("A1:AN112").Copy Sheet2.Range("A1")
        End With
    End If
    
    ActiveWorkbook.Close SaveChanges:=False
    End With
Application.Calculation = xlCalculationAutomatic
End Sub

Code the button is using:
VBA Code:
Private Sub CommandButton1_Click()
Call ImportR
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi,
move this line

VBA Code:
ActiveWorkbook.Close SaveChanges:=False

to where shown here

Rich (BB code):
With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Set ws = wkbSourceBook.Sheets(1)
         
        With Sheets(1)
            .Range("A1:AN112").Copy Sheet2.Range("A1")
        End With
        
        ActiveWorkbook.Close SaveChanges:=False
        
    End If

and see if resolves your issue

Dave
 
Upvote 0
It is not a good practice to rely on ActiveWorkbook. Instead you should make references explicit. You are closing ActiveWorkbook at the end of your code, but if no new file is opened ActiveWorkbook is the file containing your code. I have moved that line inside the If statement and changed ActiveWorkbook to explicitly reference the file you just opened.


VBA Code:
         Set ws = wkbSourceBook.Sheets(1)
The object ws is never used after it is set here. Not sure why this line is needed.

VBA Code:
        With Sheets(1)
            .Range("A1:AN112").Copy Sheet2.Range("A1")
        End With
I am assuming you are copying from the workbook you just opened into the workbook containing this code.

Rich (BB code):
Sub ImportR()

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
 
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim ws As Worksheet
  
   Set wkbCrntWorkBook = ActiveWorkbook
  
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
     
         Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
         wkbSourceBook.Sheets(1).Range("A1:AN112").Copy ThisWorkbook.Sheet2.Range("A1")
        
         wkbSourceBook.Close SaveChanges:=False
     
      End If
         
   
   End With

   Application.Calculation = xlCalculationAutomatic

End Sub
 
Upvote 0
Solution
It is not a good practice to rely on ActiveWorkbook. Instead you should make references explicit. You are closing ActiveWorkbook at the end of your code, but if no new file is opened ActiveWorkbook is the file containing your code. I have moved that line inside the If statement and changed ActiveWorkbook to explicitly reference the file you just opened.


VBA Code:
         Set ws = wkbSourceBook.Sheets(1)
The object ws is never used after it is set here. Not sure why this line is needed.

VBA Code:
        With Sheets(1)
            .Range("A1:AN112").Copy Sheet2.Range("A1")
        End With
I am assuming you are copying from the workbook you just opened into the workbook containing this code.

Rich (BB code):
Sub ImportR()

   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
 
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Dim ws As Worksheet
 
   Set wkbCrntWorkBook = ActiveWorkbook
 
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
    
         Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
         wkbSourceBook.Sheets(1).Range("A1:AN112").Copy ThisWorkbook.Sheet2.Range("A1")
       
         wkbSourceBook.Close SaveChanges:=False
    
      End If
        
  
   End With

   Application.Calculation = xlCalculationAutomatic

End Sub
Thanks for the tips! I copied this from another sheet I had so some of it was just leftover code I didn't delete.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top