turning off the _beforeclose event

Phil Riley

New Member
Joined
Apr 8, 2002
Messages
6
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?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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?
 
Upvote 0
Me again! Sussed it, I have put the ResetExcel macro call in _windowDeactivate instead. Thanks for the help!
 
Upvote 0
Also try;

<pre/>
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

</pre>
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top