CODEWITHSUNDEEP
vbaexcel
asp8811
asp8811

Reputation: 803

Excel "Workbook_BeforeClose" event not firing again after canceled

Update: After more research, I found this duplicate question: Excel 2016 Workbook.BeforeClose event firing every other time bug. It seems I was using the wrong keywords, and that this is a bug, not a problem with my code. However, I cannot seem to download the version mentioned in the solution. I am running Windows 7 and using Microsoft Office 365 Pro Plus, and Office is stating that the most up to date version available is 16.0.6965.2105

I am trying to use the Workbook_BeforeClose event to test whether a checkbox is checked or not. If not, the user is prompted on whether they want to continue closing with out checking the box. If they choose "Yes," the sheet is cleared and the workbook is saved. If they choose, "No," the box is checked and, "Cancel" is set to true.

This works fine the first time the Workbook_BeforeClose event runs. However, the second time the sheet is closed, the standard Excel "Want to save your changes to..." dialogue box comes up and the Workbook_BeforeClose event does not fire. If I click cancel on the dialogue box and close the workbook a third time, the event fires. Something is being reset when, "Cancel," is clicked in the dialogue box, but I can't figure out what it is. My code is below:

Public Closing as Boolean

Sub Workbook_BeforeClose(Cancel As Boolean)

Debug.Print "Workbook_BeforeClose"

If Closing = True Then Exit Sub     'Closing is used as a switch to stop the event from looping on Application.ThisWorkbook.Close below

Closing = True

If Sheets(1).DraftCheckBox = False Then

    If MsgBox("This file is not being saved as a draft. This workbook will be cleared if the draft box is not checked." & vbCr & vbCr & "Would you like to continue?", vbYesNo, "Warning") = vbYes Then

        'If "Yes" selected
        'Stuff happens here

        Application.ThisWorkbook.Close savechanges:=True

    Else

        'If "No" selected
        Sheets(1).DraftCheckBox = True
        Cancel = True
    End If

End If

Closing = False

End Sub

I know for a fact that Application.EnableEvents is set to True. I also know that the event itself is not firing because there is a stop on the very first line at "Debug.Print" This stop is activated after the first close and I can step through the code. It does not activate at all after the second close, either before or after the dialogue box. Is there anything that I should be doing to prevent the dialogue box from coming up and to force the Workbook_BeforeClose event every time the workbook is closed?

Upvotes: 1

Views: 1309

Answers (1)

RetiredGeek
RetiredGeek

Reputation: 3168

You are already closing the workbook you don't want to call close again, use Save instead. You also don't need the extra variable if you use the code below. One big question remains...how is your user imitating the Close event? If by the red X then the code below should work. If by a button on your form what does that button code look like?

Note: This is untested code as I don't have your workbook.

Sub Workbook_BeforeClose(Cancel As Boolean)

  If Sheets(1).DraftCheckBox = False Then
  
      If MsgBox("This file is not being saved as a draft. " + _
                "This workbook will be cleared if the draft box is not checked."  _
                & vbCr & vbCr & "Would you like to continue?", _
                vbYesNo, "Warning") = vbYes Then
  
        'If "Yes" selected
        'Stuff happens here
        
        Application.ThisWorkbook.Save
        Cancel = False 'Just to be sure.
        
      Else
          'If "No" selected
          Sheets(1).DraftCheckBox = True
          Cancel = True
      End If
  
  End If
  
End Sub

HTH

Upvotes: 1

Related Questions