Deactivate Events not Firing

[William]

I've encountered a problem with Excel where the deactivate events (for
workbook and window) don't fire in certain situations.

The steps to re-create the problem are:
1. You need to have two or more workbooks open in the same instance of Excel
2. In one of the workbooks, enter some code to detect the deactivate events
(eg. a message box) and save it
3. In another workbook, make some changes but don't save yet
4. Switch to the workbook with the VBA deactivate code and click the 'x'
(close) button for the whole Excel application
5. Excel should ask you to save the other workbook. Click 'Yes' and Excel
should close but the deactivate event doesn't get fired for the active
workbook.

Does anyone else have the same problem?

It is important for what I am doing that there is a deactivate event for
every activate event. I've not been able to find any information about this
problem. The version of Excel I am using is 2000 with SP3. I know that this
problem also occurs with Excel 2003.

If this is a known problem, can someone please direct me to where it is
documented and a workaround if available?

Thank you all

 

[Rob Bovey]

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.

--
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 Benson]

Did you test this by putting a message box in the _Deactivate event?

I have done so and find that the deactivate gives me the messagebox whether
closing the workbook or the application, and in your special example, even
after having made changes and saying Yes to Save after clicking the X's. I
am using 2003.

 

[Doug Glancy]

William,

I am able to duplicate exactly as you described it in XL 03. I can also see
what Rob is saying. What's problematic is that when you say yes to saving
the second book and it becomes active, the first book does not fire a
deactivate event, even though it was active when you got the save prompt for
the second book.

The only thing I can think of is using BeforeClose, but then you have
problems if they cancel the Close.

Doug

 

[William Benson]

Rob,

Great answer - I totally missed the active workbook issue in my answer!

In keeping with Simon's concern that a deactivate event be fired for every
time an activate event fires, he can have this reassurance: There is no way
his Deactivate has not fired by the time Excel closes either the Workbook or
the Application concerned. That is because it would have fired at the time
he either

opened another workbook so that it became the active workbook
created a new workbook so that it became the active workbook
switched from the workbook he is concerned to another open workbook
hid the workbook he is concerned with
.... can't think of any other scenarios.

Bill

 

[William Benson]

My response on this Doug was that the deactivate event has fired. Aren't the
facts that you are basically asking it to fire a second time? Once when it
was demoted (made Workbook2 active) and again when Excel is closing?

Do I have it wrong?

 

[William]

Hi William,

Yes, I put the message box in the Workbook_Deactivate event.

Did you have two workbooks open? The second workbook should be the one thats
changed and when you close the whole application while you have the workbook
with the deactivate event handler, Excel will ask if you want to save the
changes. By choosing "Yes", you don't get the messagebox.

 

[William Benson]

I have now done what you said step by step and agree... sorry for my
misguided responses earlier. If you say 'Yes' to accept the changes to
workbook B, you get no deactivate_event in workbook A. I have no answer (and
wish I'd kept my mouth shut).

Bill

 

[William]

Hi Doug,

Thats is correct. Somehow the second workbook becomes active when you click
"Yes" without the first handling the deactivate event.

 

[William]

Hi William,

No need to apologise.
Thanks for your replies.

 

[Rob Bovey]

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 procedure:

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]

Hi Rob,

Thanks for the workaround. It works fine for me.

William