Application.Quit Not Working in Office 2007

[Carl]

I have used the following code to shut down Excel 2003 when the workbook is
closed but now that I am using Excel 2007 it no longer closes Excel, only the
active workbook. I have tried everything to no avail and have the same
results whether the workbook is in 2003 or 2007 format. Thanks in advance,
Carl

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
Application.DisplayFullScreen = False
Application.Quit
End Sub

 

[Jacob Skaria]

Try saving the workbook..

ThisWorkbook.Save
Application.Quit

If this post helps click Yes

 

[Carl]

Thanks for the suggestion but I tried that and it did not work. My operating
system is Vista and I am using Office 2007. I also tried "Application.Quit"
twice. Any other ideas? Thanks.

 

[jaf]

Hi Carl,
You probably have an object that is still being used by the system.
Do you have a routine to set objects you created to nothing?

set objXL = nothing

John

 

[Carl]

I eliminated that possibility by creating a brand-new workbook as a
macro-enabled 2007 file and entering the following code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
Application.Quit
End Sub
When I close the file it closes but Excel remains open. I need Excel to
close so it will stop timed events from reopening the file the next time the
event is due to start. Thanks for taking the time to help. By the way, the
same code works perfectly in office 2003.

 

[jaf]

Hi Carl,
That was the only code in the workbook?

I'm not using 2007. Try this one line change. I know the ribbon & control bars went thru design changes in XL 2007.
It could be a bug. Or as MS call them "by design".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
Application.DisplayFullScreen = False
SET OCTRL IS NOTHING
Application.Quit
End Sub

John

 

[Carl]

Thanks for your help, jaf.

The code is the only code in my test file just to eliminate any question. I
entered the following and it still did not close Excel:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
Application.DisplayFullScreen = False
Set oCtrl = Nothing
Application.Quit
End Sub

Thanks again. Please keep those ideas coming. Carl

 

[jaf]

Carl,
Set oCtrl = Nothing should be Set oCtrl is Nothing.

John

 

[Carl]

Thanks. I tried that first but received a compile error, expected "=" instead
of "is". Replaced "is" with "=" and cleared error but still did not shut down
Excel when the workbook is closed.