Message box when quitting

Terry P

Active Member
Joined
Nov 28, 2002
Messages
256
I want to be able to offer a choice of saving or not before closing a workbook & quitting Excel when the user presses either of the x's on the workbook or application.
I have tried several ways but just can't get it to work!
Currently I have...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
ResetToStandardMenus
Application.Quit
End Sub

Any ideas please
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
How about:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim Msg, Style, Title, Response
Msg = "Do you want to save the sheet?"
Style = vbYesNo
Response = MsgBox(Msg, Style)
If Response = vbNo Then
ActiveWorkbook.Close SaveChanges:=False
Else
ActiveWorkbook.Close SaveChanges:=True
End If
Application.DisplayAlerts = true
End Sub
 
Upvote 0
Without the code you posted, Excel doesn't ask you to save changed workbooks before closing?
 
Upvote 0
tactps & Taz
Thanks for your replys.
Taz:
No, without the code the options don't show at all.(?)

tactps:
I tried your code and the only problem is that it only closes the workbook when I press either the workbook or the the application x's.
It does not quit Excel.
Ideally when either x is pressed I want the options to appear then either save or not then the application close afterwards.

If you come up with some other ideas I'll look at them tomorrow.
Thanks again.
(need beauty sleep)
 
Upvote 0
Semi resolved.
It was a conflict between workbook before close and an Auto_close sub.

Semi resolved because when I press cancel on the dialogue it shows the sheet tabs and formula bar.

Still, I can live with it.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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