Hello,
I have a few pop up questions that appear prior to running the main macro. They ask questions such as did you enter in the correct email addresses and so on. The issue is I've been testing my completed macro and I tried clicking "No" on one of them and the macro continued which destroyed hours of work. Can someone look at this code below and see why it didn't stop the macro when "No" was chosen? I marked the questions by inserting astricks on top and bottom of each question.
I have a few pop up questions that appear prior to running the main macro. They ask questions such as did you enter in the correct email addresses and so on. The issue is I've been testing my completed macro and I tried clicking "No" on one of them and the macro continued which destroyed hours of work. Can someone look at this code below and see why it didn't stop the macro when "No" was chosen? I marked the questions by inserting astricks on top and bottom of each question.
Code:
Sub ConvertCSVToXlsxInvoices()
'This macro loops through the workbook in the prenamed folder and changes the file extension from csv to xlsx.
Dim answer As Integer
Dim myValue As Variant
Dim myfile As String
Dim oldfname As String, newfname As String
Dim workfile
Dim folderName As String
'*******************************************************************************************
'Below is a warning box that warns you to make sure you dragged all the Reports into the POP UP Folder before you move forward.
answer = MsgBox("Did you download and drag all the Inventory Reports needed to process this report into the POP UP folder?", vbYesNo + vbQuestion, "WARNING!")
If answer = vbYes Then 'You clicked yes, this will make the macro continue.
Else
Exit Sub 'exit this macro until you drag the correct WB to the POP UP folder
End If
'**********************************************************************************************
'Below is a warning box that warns you to make sure you dragged all the Invoices into the POP UP Folder before you move forward.
answer = MsgBox("Did you download and drag all the Invoices needed to process this report into the POP UP folder?", vbYesNo + vbQuestion, "WARNING!")
If answer = vbYes Then 'You clicked yes, this will make the macro continue.
Else
Exit Sub 'exit this macro until you drag the correct WB to the POP UP folder
End If
'************************************************************************************************
'Below is a warning box that warns you to make sure you have the correct email addresses in the address area before you move forward.
answer = MsgBox("Do you have the correct email addresses in email address area?", vbYesNo + vbQuestion, "WARNING!")
If answer = vbYes Then 'You clicked yes, this will make the macro continue.
Else
Exit Sub 'exit this macro until you save the other workbooks needed to complete this macro
End If
'************************************************************************************************
'Below is being used for the inputbox to insert a due date for the next report time.
'This requires user to insert a next due date so they dont forget.
myValue = InputBox("Please insert the next time this report will need to be completed by!", "WARNING!! Complete before you proceed.", Range("G1").Text)
Range("G1").Value = myValue
Select Case StrPtr(myValue)
Case 0
'OK not pressed
Exit Sub
Case Else
'OK pressed
'Carries on your routine.
End Select
'**********************************************************************************************
'Below is being used for the inputbox to insert a date so this macro can create a new folder using the date.
'This requires user to insert a date so a folder can be created.
myValue = InputBox("Please insert todays date! This will create a new folder based on the date you enter.", "WARNING!! Complete before you proceed.", Range("A168").Text)
Range("A4").Value = myValue
Select Case StrPtr(myValue)
Case 0
'OK not pressed
Exit Sub
Case Else
'OK pressed
'Carries on your routine.
End Select
'***********************************************************************************************
'Below call a macro to create a new folder based on the date in cell B4.
Call MakeMyFolderInvoicesCombined
Call MakeMyFolderReportsCombined
Call MakeMyFolderFinalWorkbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Capture name of current file
myfile = ActiveWorkbook.Name
'Set folder name to work through
folderName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\JM Smuckers\Invoices Depository\"
'Below adds a backslash to the end of code above to locate the folder.
If Right(folderName, 1) <> "\" Then
folderName = folderName & "\"
End If
'Loop through all CSV filres in folder
workfile = Dir(folderName & "*.CSV")
Do While workfile <> ""
' Open CSV file
Workbooks.Open Filename:=folderName & workfile
' Capture name of old CSV file
oldfname = ActiveWorkbook.FullName
' Convert to XLSX
newfname = folderName & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".xlsx"
ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
' Delete old CSV file
Kill oldfname
Windows(myfile).Activate
workfile = Dir()
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Below will create a new WB which will be used for combining all the invoices together into one sheet.
'Adding New Workbook
Workbooks.Add
'MsgBox "Please research all the rows that are displayed here in RED.", , "MUST BE DONE TO COMPLETE REPORT!"
'MsgBox "Once completed go back to the main workbook and click on the next button.", , "MUST BE DONE TO COMPLETE REPORT!"
End Sub
