How to replace the standard Exel dialog box when closing a workbook ?

[Philfrench]

Hi all,

I am looking for a way to replace the standard dialog box (save or not) by a simple request for confirmation when closing a workbook by the x-button of the workbook without using the x-button of Excel.
I have found solution but if another workbook is opened, after having closed the first one, I can't do anything with the displayed workbook.

Thanks in advance for your help.

 

[Chattin]

Haven't tested this so have a play in a blank workbook and enter some random info to see if its saved or not

Sub CloseWorkbook()
Application.DisplayAlerts = False
If MsgBox("Save changes?", vbQuestion + vbYesNo, "&quot = vbNo Then
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
Exit Sub
Else
ActiveWorkbook.Close SaveChanges:=True
Application.DisplayAlerts = True
End If
End Sub

 

[Philfrench]

Hi Chattin,

Thanks for your help but I have tried it and it gives the same result I got previouly. If you answer No, first you come back to the same dialog box due to the new close and when you answer no for the second time, the Workbook closes but if their is another workbook opened, you can do nothing (even close) with it until you ask another application like Word and then come back to Excel.

 

[TonyJollans]

Hi Philfrench,

I don't think you should be doing a workbook close in the beforeclose event for that workbook. Just save or not according to the User's reply and let Excel get on with doing the close. With a very limited bit of testing this appears OK ..

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If MsgBox("Save changes?", vbQuestion + vbYesNo, "") = vbNo Then
    Me.Saved = True
Else
    ActiveWorkbook.Save
End If

End Sub

Enjoy,
Tony

 

[Philfrench]

Hi Tony,

Thanks a lot, it works fine and it is so simple !
I have just modified it to do what I wanted to do (asking for confirmation) :

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If MsgBox("Confirm ?", vbQuestion + vbYesNo, "&quot = vbNo Then
Cancel = True
Else
Me.Saved = True
End If

End Sub

 

[Chattin]

You have your answer but just for some background see

http://support.microsoft.com/default.aspx?scid=kb;en-us;129153&Product=xlw97

HTH