Macro to exit sub when cancel button is selected

kalcerro_1

New Member
Joined
Feb 28, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have this macro, parts mine, parts with help here.
I'm trying to improve the code so when the user selects the cancel button after the "File To Import" window appears, the macro runs the "exit sub" command. I have tried a few lines of code after application.getopenfilename line, but I always have the error.
So, Here is the code, feel free to ask or comment, and thank you for the help in advance:


VBA Code:
Private Function ShEx(sn$) As Boolean

'SubRoutine to find if there is a tab with same name

    Dim sh As Worksheet
        For Each sh In Worksheets
            If StrComp(sh.Name, sn$, vbTextCompare) = 0 Then
                ShEx = True
                Exit Function
            End If
        Next
End Function

Private Sub CommandButton1_Click()
  'SubRoutine to select new tab and insert it in workbook
 
 If ShEx("4. JiraResults") Then
        MsgBox "Sheet '4. JiraResults' already exists!", 48, "Error"
        Exit Sub
    End If
    
    Dim FileToImport As Variant
    FileToImport = Application.GetOpenFilename(FileFilter:="XLS's  (*.xls), *.xls", Title:="Select file to import")
    
    MsgBox "This process will take a few seconds, please wait", vbOKOnly

    Application.ScreenUpdating = False
    
    Workbooks.Open FileToImport
        Sheets("general_report").Select
        Sheets("general_report").Name = "4. JiraResults"
        Worksheets("4. JiraResults").Activate
        ActiveSheet.Range("a4").Select
        ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
        ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
        Selection.RowHeight = 15
    With Selection.Font
        .Name = "Calibri"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
            
    ActiveWindow.DisplayGridlines = False
    Sheets("4. JiraResults").Select
    Sheets("4. JiraResults").Move Before:=Workbooks( _
        "2020 SKILLS MATRIX - GISD.xlsm").Sheets(6)
   
   ActiveSheet.Range("a4").Select

Sheets("TechAsseResults").Range("l3").Formula = "=COUNTIF('4. JiraResults'!d5:d3000,TechAsseResults!k3)"
Sheets("TechAsseResults").Range("l4").Formula = "=COUNTIF('4. JiraResults'!d5:d3000,TechAsseResults!k4)"
Sheets("TechAsseResults").Range("l5").Formula = "=COUNTIF('4. JiraResults'!d5:d3000,TechAsseResults!k5)"
Sheets("TechAsseResults").Range("l6").Formula = "=COUNTIF('4. JiraResults'!d5:d3000,TechAsseResults!k6)"

Application.ScreenUpdating = True
       
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Rich (BB code):
 FileToImport = Application.GetOpenFilename(FileFilter:="XLS's  (*.xls), *.xls", Title:="Select file to import")
      If FileToImport = False Then Exit Sub 
 MsgBox "This process will take a few seconds, please wait", vbOKOnly
 
Upvote 0
Solution
Rich (BB code):
FileToImport = Application.GetOpenFilename(FileFilter:="XLS's  (*.xls), *.xls", Title:="Select file to import")
      If FileToImport = False Then Exit Sub 
MsgBox "This process will take a few seconds, please wait", vbOKOnly
thank you @JLGWhiz that worked perfectly!
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

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