It takes two clicks to shut down Excel

[Dean Meyer]

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?

 

[Jim Cone]

Add "Cancel = True" to your code.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Dean Meyer"
<[email protected]>
wrote in message
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?

 

[Dean Meyer]

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.

 

[Dean Meyer]

Thanks for trying to help, Jim.

"Cancel" is not a defined parameter of Workbook_WindowDeactivate so I
can't add it there.

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!

 

[Dean Meyer]

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!

Can anybody replicate? Any clues as to why or a work-around?

 

[Jim Cone]

I never said it was.
The only events you mentioned in your original post were BeforeClose and BeforePrint.
Jim Cone
San Francisco, USA


"Dean Meyer" <[email protected]>
wrote in message
Thanks for trying to help, Jim.
"Cancel" is not a defined parameter of Workbook_WindowDeactivate so I
can't add it there.
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!

 

[Jim Cone]

That should have read... were BeforeClose and BeforeSave.
Jim Cone

"Jim Cone"
<[email protected]>
wrote in message
I never said it was.
The only events you mentioned in your original post were BeforeClose and BeforePrint.
Jim Cone
San Francisco, USA

 

[Dean Meyer]

Jim, thanks again for trying to help.

You're absolutely correct; my first message was misleading. After more
work, I was able to isolate the problem to the
Workbook_WindowDeactivate or Workbook_Deactivate events. If you're
still of a mood to help, have a look at my 3:46PM message above.

Thanks again,

--Dean

 

[Dean Meyer]

I think the bug is as simple as this:

If a workbook traps the Workbook_Deactivate or
Workbook_WindowDeactivate events in its VB code,

and if that workbook needs to be saved (changes have been made),

and if that workbook is not active (any other workbook is active,

THEN the big red X to close Excel will bomb, but the next red X will
save and close the files.

I spent 3.5 hours on the phone trying to get someone at Microsoft
qualified to talk to me -- not only no luck (all customer support and
technical support were not competent to deal with VB), but at the end
they were rude about it. They said I'm not qualified to recognize a
bug, and if I want to report it, I'd have to pay them for "support"! So
much for the evil empire!

I then spoke to a friend at Microsoft headquarters. He said they're not
the least bit interested in fixing Excel 2003 now that 2007 is out.

What fun... we get to discover a whole new set of bugs in 2007!

I thought of using the Application_WorkbookActivate event to catch a
deactivate on the way into the next workbook. But if you're working
with a set of workbooks and close the first one you loaded, the event
trap disappears (it's not passed along to the next in line)! Another
bug, I presume.

Anyhow, I could find no fix and no workaround. So I'm going to have to
do without the functionality I'd intended based on the Deactivate
events.

--Dean

 

[Jim Cone]

Dean,
I still don't know exactly what you are trying to do, however
you may want to experiment with the "OnWindow" event.
It runs whenever the specified window is activated....
(in a standard module)

Sub IsItTrue()
Windows("CaptionAtBottomOfWindowMenu").OnWindow = "CallMe"
End Sub

Sub CallMe()
MsgBox "I am here. "
End Sub
'-----------

It's possible that the no longer documented OnSheetActivate and
OnSheetDeActivate events might be of use...

Sheets("MySheet").OnSheetActivate = "NameOfSubToCall"
Sheets("MySheet").OnSheetActivate = "" 'Turns if off.
'------------
Also,
In the for what its worth department... I just installed XL2003 to go along
with the three other versions I have installed. However,Service Pack 2 refused
to install when I updated the program. My telephone call to Microsoft
was answered promptly and the technician was able to fix the issue and
do it courteously. (It was 4:30 am in India.)
I even got a follow up call the next day. The whole experience was an
impressive one on their part. I have my own list of complaints
about MS, but customer service is not one of them.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Dean Meyer" <[email protected]>
wrote in message
I think the bug is as simple as this:
If a workbook traps the Workbook_Deactivate or
Workbook_WindowDeactivate events in its VB code,

and if that workbook needs to be saved (changes have been made),

and if that workbook is not active (any other workbook is active,

THEN the big red X to close Excel will bomb, but the next red X will
save and close the files.
I spent 3.5 hours on the phone trying to get someone at Microsoft
qualified to talk to me -- not only no luck (all customer support and
technical support were not competent to deal with VB), but at the end
they were rude about it. They said I'm not qualified to recognize a
bug, and if I want to report it, I'd have to pay them for "support"! So
much for the evil empire!
I then spoke to a friend at Microsoft headquarters. He said they're not
the least bit interested in fixing Excel 2003 now that 2007 is out.
What fun... we get to discover a whole new set of bugs in 2007!
I thought of using the Application_WorkbookActivate event to catch a
deactivate on the way into the next workbook. But if you're working
with a set of workbooks and close the first one you loaded, the event
trap disappears (it's not passed along to the next in line)! Another
bug, I presume.
Anyhow, I could find no fix and no workaround. So I'm going to have to
do without the functionality I'd intended based on the Deactivate events.
--Dean