There's probably a more robust way to do this, but this should provide a
starting point. The key is to set Cancel = True.

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)

Cancel = True
MsgBox "Saving"
Wb.Save
xlApp.EnableEvents = False
Wb.Close False
xlApp.EnableEvents = True

End Sub

"Eric Smith" wrote in message
...
I'm working on an Excel add-in and found some unexpected behavior that I
hope
someone might be able to explain to me.

The add-in handles the WorkbookBeforeClose event, and possibly saves the
Workbook. This works fine if the close is done manually -- that is, by
clicking the 'X' button. But if the close is done programmatically through
a
call to Workbook.Close, then saving doesn't work.

I've written some VBA that replicates the problem (though to be clear, the
add-in is more complicated, doing more than just a simple save).

First is a class called XLEvents.

Code:

Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
MsgBox "Closing"
Wb.Save
End Sub

I also have a macro that simply closes the Workbook. This isn't part of my
add-in's behavior -- it just illustrates the issue, and is something that
could happen if the user has their own macro, another add-in, etc.

Code:

Sub CloseWorkbook()
Application.ActiveWorkbook.Close
End Sub

This is the behavior I see:

Manual

1. Open the workbook.
2. Make a small edit.
3. Close the workbook by clicking the 'X'.
4. The "Closing" message box appears; click OK.
5. The workbook closes.

Programmatic

1. Open the workbook.
2. Make a small edit.
3. Run the "CloseWorkbook" macro.
4. The "Closing" message box appears; click OK.
5. Excel asks if I want to save.

#5 in the programmatic case is a symptom that the save didn't do anything
in
this case. I can also verify that nothing got written to disk. Why would
this
be? Is there a way to cause the programmatic behavior to match the manual
behavior (such that Save works)?

Thanks,
Eric