Quit Excel on closing workbook

[sharky12345]

I'm currently using this to close a workbook;

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?

 

[Sektor]

.

 

[Sektor]

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.

 

[sharky12345]

That works - many thanks.

 

[ZVI]

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.

' 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

 

[Sektor]