Close w/o saving changes

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi,
I’m trying to run a procedure from a workbook (ThisWorkbook), as follows:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "Personal.xls!EndIt", Cancel
End Sub

The code in Personal.xls (standard module) is:

Sub EndIt(Cancel As Boolean)
On Error GoTo Err_EndIt
Set fso = Nothing
Set shSeason = Nothing
If MsgBox("Do you want to save changes to this workbook?", vbYesNo,
Title:="Save LPM Workbook") = vbYes Then
ActiveWorkbook.Save
End If
Application.CommandBars("Ply").Enabled = True
Application.Caption = Empty
Exit_EndIt:
Exit Sub
Err_EndIt:
Call LogError(Err.Number, Err.Description, "EndIt(Cancel As Boolean)")
Resume Exit_EndIt
End Sub

I want to save the active workbook, if the User selects “Yesâ€, or not save
it, if the User selects “Noâ€.
The save and close on “Yes†works correctly, but the close on “No†sends up
a MSO message box asking me if I want to save changes. How do I suppress the
MSO message box? Thanks.
Mark
 
Try this:

If MsgBox("Do you want to save changes to this workbook?", vbYesNo, _
Title:="Save LPM Workbook") = vbYes Then
ActiveWorkbook.Close Savechanges:=True
Else
ActiveWorkbook.Close Savechanges:=False
End If

Regards,
Stefi


„Mark†ezt írta:
 
Thanks, Stefi. That does work, except that after you answer the message box,
the same message box appears again. I'd like to be able to do away with the
message box appearing a second time.
Mark
 
not sure where you have placed your code but it should work.

see if this helps - Taken from helpfile:

This example closes the workbook that contains the example code and discards
any changes to the workbook by setting the Saved property to True.

ThisWorkbook.Saved = True
ThisWorkbook.Close
 
Hi John,
I'm putting the code in the workbook_beforeclose event right after the call
to Personal.xls. Unfortunately, it still doesn't work.
Mark
 
Back
Top