Nested Ifs on Workbook_BeforeClose

[jmr98]

Hi - I'm trying to make a workbook remain open if the user wants to fix mistakes before submitting the file. This is my current code - a couple of queries are refreshed, the user is asked if the input is complete, and then a cell is checked to see if there are errors. If there are errors, a message box comes up asking if they'd like to keep the file open and fix the errors. The vba works up to this point, but I can't get the "cancel = True" to keep the file open. It merely saves and closes.

The additional complication is that, if the file is error-free, the file can be submitted per another message box. This part currently works as well when the file has no errors. It's just the getting the file to stay open that I'm struggling with. Would appreciate any help! Here is my code! The "saveas2" at the bottom is just another macro that saves the file in a certain spot.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
   ActiveWorkbook.Connections("Query - New").Refresh
   ActiveWorkbook.Connections("Query - New1").Refresh

    If (MsgBox("Is the input complete?", vbYesNo) = vbNo) Then
    Exit Sub
    Else
        If Range("PYChecker") > 0 Then
        aok = MsgBox("Before the file is submitted, you will need to fix your errors. Would you like to do so now?", vbYesNo)
        If (aok = vbYes) Then
        Cancel = True
        Exit Sub
        End If
        End If
        End If
        
        If (MsgBox("Would you like to submit the file?(Just click yes if so)", vbYesNo) = vbYes) Then
        If (MsgBox("Thank you! When you click OK, this file will be submitted.", vbOKOnly) = vbOK) Then
        saveas2
            
        End If
      
        

    
 
    

    End If
  
    
  

    
    
End Sub

 

[John_w]

The vba works up to this point, but I can't get the "cancel = True" to keep the file open. It merely saves and closes.

That suggests you haven't put Cancel = True in the correct place(s).

I'm not sure if I've got all the logic correct, because it isn't totally clear from your description, but try this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  
    ActiveWorkbook.Connections("Query - New").Refresh
    ActiveWorkbook.Connections("Query - New1").Refresh
    Stop
    
    If MsgBox("Is the input complete?", vbYesNo) = vbNo Then
        Cancel = True
    Else
        If Range("PYChecker") > 0 Then
            If MsgBox("Before the file is submitted, you will need to fix your errors. Would you like to do so now?", vbYesNo) = vbYes Then
                Cancel = True
            End If
        ElseIf MsgBox("Would you like to submit the file? (Just click yes if so)", vbYesNo) = vbYes Then
            If MsgBox("Thank you! When you click OK, this file will be submitted.", vbOKOnly) = vbOK Then
                saveas2
            End If
        End If
    End If
    
    MsgBox "Cancel = " & Cancel & vbCrLf & "Workbook will " & IIf(Cancel, "not ", "") & "be closed", , "Workbook_BeforeClose"
        
End Sub

Note that for debugging purposes I've added a Stop statement, allowing you to step through the code when you close the workbook, and a final MsgBox which displays the Cancel value.

Also, you might need a ThisWorkbook.Save somewhere in the code to save the workbook in its current location, because as written only the saveas2 call saves the workbook.

 

[jmr98]

Thanks so much.. this did not work but I see what you are getting at.. although now I'm trying to strip everything out and even this does not work. The message box comes up, I click yes, and then the file simply closes anyway. Seems crazy. Revised (shortened) code: - I must be missing something obvious at this point.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 

 
            If MsgBox("Before the file is submitted, you will need to fix your errors. Would you like to do so now?", vbYesNo) = vbYes Then
            Cancel = True
            End If



        
End Sub

 

[John_w]

The message box comes up, I click yes, and then the file simply closes anyway

I can't explain that behaviour. The fact that the message box is displayed means the event is being triggered (Application.EnableEvents is True) when you close the workbook and clicking Yes should keep it open.

I would try that simplified code in a new workbook and if it works it suggests your original workbook is corrupted.

 

[jmr98]

Thanks John. I put it in a new workbook and it did the same thing. Message box comes up, I select yes, workbook closes anyway. I can't get the cancel = true to work. This is so odd.

 

[dmt32]

I can't get the cancel = true to work. This is so odd.

Hi not fully tested but see if this update to your code will do what you want

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
   ActiveWorkbook.Connections("Query - New").Refresh
   ActiveWorkbook.Connections("Query - New1").Refresh
    
    Cancel = MsgBox("Is the input complete?", 292, "Input Complete") = vbNo
    
    If Not Cancel And Range("PYChecker") > 0 Then
        Cancel = MsgBox("Before the file Is submitted, you will need To fix your errors." & Chr(10) & _
                 "Would you Like To Do so now?", 36, "Fix Errors") = vbYes
    End If
    
    If Not Cancel Then
        If MsgBox("Would you Like To submit the file?" & Chr(10) & "(Just click yes If so)", 36, "Submit File") = vbYes Then
            MsgBox "Thank you! When you click OK, this file will be submitted.", vbOKOnly
            saveas2
        End If
    End If

End Sub

Dave

 

[jmr98]

Thanks everyone. I'm so sorry, it looks like my issues are being caused by an add-in that I'm now trying to figure out how to disable via VBA. I know that's a separate topic, so I may open a new thread as the proposed solutions to that haven't been working for me. Thank you all!