I'm not sure how to handle the output from a MsgBox in order to allow my macro to continue processing given differing situations.
This macro creates a daily report file with an output name of abcdMMDD.xls.
Early in the script I check for a file in the output directory with the same name as the day's output file and show a message box to the user asking if they mean to overwrite the file. Based on their response the script continues in either of two ways.
The important code is:
If ChkFileThere("G:\Management Reporting\Asset Comparison Report\DAILY\" & FileName) Then If MsgBox("File " & FileName & " already exists. Continue and overwrite?", vbYesNo, "Output File Exists") = vbNo Then GoTo ProcessFailed2 Else: GoTo ProcessStep2 End If
The problem is this:
When the output file already exists, the prompt is displayed properly, but pressing YES (as in Yes, please overwrite) the macro stops processing, if I press No, the expected message shows and the macro exits as designed.
If the output file does not already exist, the script continues without any prompting as designed and processes the file.
Swapping ProcessFailed2 and ProcessStep2 and changing the vbNo to vbYes at the end of the MsgBox line causes a different set of problems.
In this case if the output file already exists the Msgbox displays properly and selecting Yes allows the macro to process as expected, overwriting the output file with the newly create one.
But if the output file does not exist the script passes straight to ProcessFailed2.
I suppose the problem is which If statement the Else is included in. How do I specify which If gets the Else?
I assumed that given the first If is a block If and the second is a single-line If, the Else would be included in the block If. But I guess that is incorrect.
UPDATE: Thanks for Neal.
The following code resolves the issue currently:
ProcessStatus = "Checking if report output file already exists." Application.StatusBar = ProcessStatus If ChkFileThere("G:\Management Reporting\Asset Comparison Report\DAILY\" & FileName) Then If MsgBox("File " & FileName & " already exists. Continue and overwrite?", vbYesNo, "Output File Exists") = vbNo Then GoTo ProcessFailed2 End If Else: GoTo ProcessStep2 End If
Based on Neal's recommendation though, the last outstanding question is: When using an IF/AND statement, is the AND portion only processed if the first portion is found true?