Quit Excel on closing workbook

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
I'm currently using this to close a workbook;

Code:
ActiveWorkbook.Close SaveChanges:=True

This leaves an instance of Excel running which I know can be resolved by using Application.Quit. My questions is though when I've used that in the past it closes every Excel workbook that is open when in fact all I want is the active one closed, so is there a way I can get it to quit Excel if it detects that I only have 1 active workbook?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
Well, what you need is to track Excel Application events. Here's workbook which does this thing.
It listens to Excel event - WorkbookBeforeClose, which checks how many workbooks left. If there was only one workbook, then Excel is quit.
 
Last edited:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
sActually the code line If Workbooks.Count = 1 Then Application.Quit is not always enough to quit Excel because a Personal Macro Workbook should also be taken into the account.
If present it gives Workbooks.Count = 2

Here is one more solution:
1. Create a Personal Macro Workbook
2. Put the below code into its ThisWorkbook module and press Ctrl-S to save that workbook.
3. Reload Excel or just run Workbook_Open macro in the suggested code.
Rich (BB code):
' Put the below code into ThisWorkboook module of the PERSONAL.XLSB and save
Public WithEvents App As Application
 
Private Sub Workbook_Open()
  Set App = Application
End Sub
 
Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
  If Not Me.Windows(1).Visible Then
    If Workbooks.Count = 2 Then
      App.OnTime Now, ThisWorkbook.CodeName & ".QuitMe"
    End If
  End If
End Sub
 
Private Sub QuitMe()
  App.Quit
End Sub
 
Last edited:

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
hi.gif
 

Watch MrExcel Video

Forum statistics

Threads
1,127,838
Messages
5,627,176
Members
416,227
Latest member
Abid Fattani

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top