Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
This isn't a use case for GoTos, that way lies madness. If you give the larger layout of the code then I can show you how to rearrange it (include the ProcessStep2 and Process Failed2 parts). – Lance Roberts Apr 7 '11 at 17:20
Oh there's a good deal of code there and no doubt you'd find many areas to rearrange and improve the logic. I'm very new to VBA (I failed VB and C+ close to 15 years ago and have only done HTML/CSS web "programming" since then) and am learning. I'm still a bit tickled I'm finding it relatively easy to read through the code and understand a good deal of what is going on. Currently my focus is on making the system work well for the end users and handling errors in a descriptive way effectively. I'll work on the code logic in subsequent projects. – music2myear Apr 7 '11 at 20:15
Yes, but the problem looks like a code logic problem. If you rebuild the ProcessStep2 and ProcessFailed2 as functions, then rebuild your IF statement, it would really simplify things and if it didn't work then, you'd be able to easier see what the problem is. – Lance Roberts Apr 7 '11 at 20:23
up vote 1 down vote accepted

As I remember it, the best way to deal with the nested ifs is to have all ifs as multi-line ifs, ie move on to a new line after the then.

One can then use end if to specify that one does not want the following else to be related to the immediately preceeding if, but one higher up the chain.

if Test1 then
    if Test2 then
        do something
    end if
    do something else if Test1 is false
end if

Actually, I'd rather avoid the nested ifs by using one if statement and an and ie something along the lines of:

if FileExists and UserDoesntWantToOverWrite then
end if
share|improve this answer
Ok, I broke all the If statements out to block Ifs and used End If to end each where I wanted. This appears to have resolved the issue. But you're saying I could use AND to apply multiple conditions to a single If. Would the second condition of the If only run if the first condition was true? – music2myear Apr 7 '11 at 20:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .