turning off the _beforeclose event

[Phil Riley]

I am trying to get this piece of code to work:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
myans = MsgBox("Do you want to save the changes you made?", 67)
If myans = vbYes Then
Sheets(1).Range("A1") = ""
ActiveWorkbook.Save
ResetExcel
ActiveWorkbook.Close
ElseIf myans = vbNo Then
ResetExcel
ActiveWorkbook.Close
End If
Cancel = True
End Sub

..but when I run it, the ActiveWorkbook.Close command triggers the whole routine again! Can I put a line in just before which will stop this happening?

[Mark O'Brien]

You're already in the process of closing your workbook, you don't need those "Close" statements in there. If you really want the workbook to close get rid of that "Cancel = True" statement as well. This cancels the close action.

[Phil Riley]

Thanks Mark. That works fine if I choose "Yes" to the save changes message, but if I choose "No", I get two messages, one from my code, and one from Excel. I know there is a way to disable events, but I can't find it/ remember. can anyone help?

[Phil Riley]

Me again! Sussed it, I have put the ResetExcel macro call in _windowDeactivate instead. Thanks for the help!

[Ivan F Moala]

Also try;

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If Not ThisWorkbook.Saved Then

        Select Case MsgBox("Do you want to save the changes you made? ", 67)

            Case vbCancel

                Cancel = True

                Exit Sub

            Case vbYes

                resetexcel

                ThisWorkbook.Save

            Case vbNo

                resetexcel

                ThisWorkbook.Saved = True

        End Select

    End If

    

End Sub