It takes two clicks to shut down Excel

[NDeanMeyer]

I have a workbook with a bunch of VB code in it.

I have this workbook and any other workbook open, but this workbook is
not active. I click the X to shut down Excel. It calls the
Workbook_BeforeClose event, then asks if I want to save my work (as it
should). I select "Save All" and the shut down is aborted. It never
makes it to the Workbook_BeforeSave event.

I then click the X a second time, and I'm not prompted to save but the
shut down proceeds with saving all and then closing Excel.

==> Why does it require a second click of the X?

If this workbook is active, all works fine.

I've commented out all event code. Didn't help.

I've deleted my commandbar menu before attempting to close. Didn't
help.

The order in which the workbooks were opened doesn't matter.

What might I be doing in VB to cause this behavior?

 

[the magician]

What might I be doing in VB to cause this behavior?

This first step in getting help is admitting you have a problem.
The second step is admitting it's your fault.

I click the X to shut down Excel. It calls the
Workbook_BeforeClose event, then asks if I want to save my work (as it
should). I select "Save All" and the shut down is aborted. It never
makes it to the Workbook_BeforeSave event.

You say you commented out all event code. You refer to these two events? Any others that might be in the mix and still active?

If this workbook is active, all works fine.

What if the other workbooks are all blank? If you created a Book1 and Book2 and left them blank, and repeated the test, do you still have problems?

I risk stating the obvious to say there's something in the "bad" workbook that disagrees with being inactive at the time of shutdown. The scientific approach would be to comment out all code and if the shutdown works, start uncommenting until the problem recurs.

 

[NDeanMeyer]

Clue: Has to do with WindowDeactivate event

Thanks for pitching in and helping me!

I found a clue: The problem occurs if ThisWorkbook contains a Workbook_WindowDeactivate or an Application_WindowDeactivate procedure, EVEN IF THERE'S NO CODE IN IT!

 

[the magician]

Typical Microsoft. A 'non-event' causes problems. If you clean it out, it works then?

 

[NDeanMeyer]

Trapping WindowDeactivate is the problem

If there is no code whatsoever to trap a WindowDeactivate event, there's no problem.

If the follow code exists, the problem occurs:


Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
End Sub

Using the Application event causes the same problem.

I suspect it's something to do with Excel trying to Deactivate a window that's not active. But I'm really struggling to think of a work-around other than giving up on using this event. Ideas?

 

[the magician]

It might be me (it often is) but:
1) I don't seem to have the problem, if I put that non-code event in a workbook, with two others open, one of them the active workbook, and I "X" out of Excel.
2) Why have any event code without code?

Anyway, from the Help file on the WindowDeactivate event:
Occurs when any workbook window is deactivated.
Since the window of the workbook in question is already deactivated, this event wouldn't be called unless it was activated in the save/close process, and then deactivated as it moved on in the save/close event. Again, it works for me, but that's not surprising, since I've got my own workbooks without your code et al.

 

[NDeanMeyer]

Obviously, an event trap with no code is just for debugging purposes, to see if the problem was caused by my code. It wasn't my code.

I loaded Excel fresh, started with a blank workbook and typed the following into the VB ThisWorkbook object:

Sub Workbook_WindowDeactivate(ByVal Wn As Window)

End Sub

Then I opened any other Excel workbook, made some edit to force a save, and clicked the big red X.

The problem occured!

You weren't able to replicate?

 

[the magician]

Seems okay by me, but there's a 100 different things at play here. Let's coordinate. The minutiae of the testing process can be tricky. here's what I do:

I create a new workbook (book1) and save it and close it. I then "open" a new blank workbook (book2). I go to code view and paste in

Sub Workbook_WindowDeactivate(ByVal Wn As Window)

End Sub

I paste this into the ThisWorkbook section. I close the VBA screen. I save the workbook. Now I open my other workbook (book1), make a change to force the save, and click the big red X. I am prompted to save book1, say yes, and Excel closes w/o problem.

 

[NDeanMeyer]

Excellent test. Now try opening both those workbooks. Make an edit in both. Choose the one without the code as active. Now click the X and tell me what happens.

 

[the magician]

Very strange. On occasions, I was able to exit with one click, depending on whether I clicked Yes to All, or Yes for each workbook. But most of the time it doesn't matter, it doesn't close!!! until the second click of the big red X.

AND - after the first click, and it doesn't close, if you make any changes to either workbook and click the X again, it closes AND saves your changes WITHOUT asking.

Wonder if this happens in Excel for Macs???

I still have rare occasions when it closes and ... you know, I "think" I'm doing it the same way each time, but.....