Problem with Workbook_BeforeClose event

[tempest0123]

Hi all.

I am having trouble figuring out a strange behavior with the
Workbook_BeforeClose event procedure.

Suppose I have a Workbook_BeforeClose event procedure that makes some
changes to some worksheets and then save the workbook before closing.
The changes made by the event procedure would be saved only if the
workbook is closed manually in Excel by a user.

However, if the workbook is opened and then closed by an external VB
program using Excel application object, changes made by the
Workbook_BeforeClose event procedure are NOT saved. In fact, the
modification date/time of the Workbook file remains unchanged.

To make sure the Workbook_BeforeClose ran if the workbook was closed
by the external program, I placed several MsgBox statements in the
Workbook_BeforeClose event procedure. And sure enough all the MsgBox
messages were displayed, but changes were not saved.

Does anyone know what is going on here with the Workbook_BeforeClose
event?

Thanks.
Jason

 

[Charlie]

It sounds like the changes are being made in the _BeforeClose procedure but
the workbook is not being saved. In your app are you doing

MyWorkbook.Save

before

MyWorkbook.Close

....just a hunch

 

[tempest0123]

It sounds like the changes are being made in the _BeforeClose procedure but
the workbook is not being saved. In your app are you doing

MyWorkbook.Save

before

MyWorkbook.Close

...just a hunch

No. The save is done at the end of BeforeClose procedure.
What is weird is that debug MsgBox I placed at before and after the
save statements both displayed but the save was not done.

For example:

Sub Workbook_BeforeClose()
Make some changes to the workbook here.

MsgBox "BeforeSave"
ThisWorkbook.Save 'Changes made to the workbook should be but
they are not.
MsgBox "AfterSave
End Sub"

Anyway, since I have posted similar questions before and no one seems
to know the solution I will just do things differently in the
workbook.

-JL