Hi Rob,

Thanks for the workaround. It works fine for me.

William

"Rob Bovey" wrote:

Hi William,

OK, now I understand how to reproduce what you're seeing. You've
definitely found a bug in the Excel object model. Fortunately, I think you
can work around this, but it's not going to be pretty.

What you need to do is have the Workbook_BeforeClose event substitute
for the Workbook_WindowDeactivate event whenever the workbook is closing
(since the Workbook_BeforeClose event fires reliably in all cases). This
requires the following logic in the Workbook_BeforeClose event procedu

1) Write custom workbook save handling code, at the end of which you either
bail out or set the Saved property of the workbook to True, depending on the
user's response. This prevents Excel from asking the user if they want to
save the workbook after the BeforeClose event has fired, potentially
allowing them to cancel the close.

2) Have the Workbook_BeforeClose event run the code you normally run in the
Workbook_WindowDeactivate event.

3) Add a module-level flag variable that is set to True by the
Workbook_BeforeClose event. The Workbook_WindowDeactivate event will check
the value of this variable and bail out immediately if it has been set to
True. This prevents your deactivate logic from being executed twice if the
Workbook_WindowDeactivate event does fire after the Workbook_BeforeClose
event.

Here's a very rudimentary example of the code behind the ThisWorkbook
object that you'd use to implement the items above:

Private mbBailOut As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lAnswer As Long
''' Custom save handler.
lAnswer = MsgBox("Do you want to save?", vbYesNoCancel)
If lAnswer < vbCancel Then
If lAnswer = vbYes Then
''' Display the GetSaveAsFilename dialog with
''' more ugly logic to handle cancels, overwrites,
''' invalid file names, etc.
Else
Me.Saved = True
End If
''' Execute deactivate logic here.
''' Set flag variable so Workbook_WindowDeactivate
''' procedure will bail out.
mbBailOut = True
Else
Cancel = True
End If
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
''' Do not execute if flag variable is True.
If Not mbBailOut Then
''' Otherwise execute deactivate logic here.
End If
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"William" wrote in message
...
Hi Rob,

Thanks for your reply.
I understand that a deactivate event wont fire if it is not the active
workbook.
In the recreation of the problem I described, the workbook with the VBA
code
to detect the deactivate event is made active before I close it. I get
asked
if I want to save the other workbook while the first workbook is still
active
and when I click "Yes", there is no deactivate event.

Its possible that my explanation isn't clear. Here is the workbook setup I
had to recreate the problem.
1. I have two workbooks, "A" and "B", "A" has the VBA code to detect the
deactivate event (a messagebox). "B" is a normal workbook without the
code. I
have modified it but I have not saved it
2. I switch to "A" and save it. I then close the whole Excel app using the
"x" on the top right-hand corner
3. I get asked if I want to save "B". The window still shows the contents
of
"A". No deactivate event is fired
4. I click "Yes", "B" is saved and Excel closes down without any
deactivate
event being fired in the process for "A"

Am I right in assuming that "A" should catch a deactivate event?

"Rob Bovey" wrote:

Hi William,

A deactivate event can only be fired from an active workbook. In the
case described below, a deactivate event would fire in the first workbook
when you created the second workbook. You won't get another deactivate
event
in the first workbook when the entire Excel application is shut down,
because that workbook isn't active when Excel closes it.

You will get a deactivate event in the new unsaved workbook, because
that is the active workbook when Excel closes. I hope that all made
sense.
If you put message boxes in the Workbook_BeforeClose and
Workbook_WindowDeactivate events of both workbooks you'll see what I
mean.