kalcerro_1
New Member
- Joined
- Feb 28, 2020
- Messages
- 27
- Office Version
- 365
- Platform
- 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:
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