Auto Close Personal Workbook

[eforti]

Hello All,
In Excel 2007 when you try to close Excel you have to close it twice because your personal workbook is open. I realize this is because I have all windows shown in my taskbar, but that's not a feature I want to give up. I'm trying to find a code I can place in my personal workbook that will make it close excel when it's the only workbook active. The below suggestion is all I've found, but I'm not sure how to execute it.

"Create code for the Workbook.SheetDeactivate Event, that checks if
ActiveWorkbook.Sheets.Count =
(1 or 0, you'll have to play with this to see which works)

Then have it Close the Workbook if it's true."

The concept makes sense to me, but I just don't understand how to make it happen.

Thanks in advance

 

[RoryA]

You could just Shift+click the Close button.

 

[eforti]

Thanks for the feedback rorya. I had read that in another post too. While you are absolutely correct I'm hoping for something even more idiot proof (once the coding is installed of course).

 

[ZVI]

1. Copy the code into ThisWorkboook module of the PERSONAL.XLSB
2. Into ThisWorkboook module press Ctrl-S to save PERSONAL.XLSB
3. Quit and reload Excel 2007 to autorun Workbook_Open event subroutine
4. Close workbook to autorun App_WorkbookDeactivate event subroutine

After testing just comment If MsgBox part of the code

' Put code into ThisWorkboook module of the PERSONAL.XLSB and save

Dim WithEvents App As Application

Private Sub Workbook_Open()
  Set App = Application
End Sub

Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
  If Workbooks.Count <= 2 Then
    If MsgBox("Quit Excel?", vbYesNo + vbQuestion) = vbYes Then
      Application.Quit
    End If
  End If
End Sub

 

[ZVI]

More correct updated code:

' Put 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
      If MsgBox("Quit Excel?", vbYesNo + vbQuestion) = vbYes Then
        App.OnTime Now, ThisWorkbook.CodeName & ".QuitMe"
      End If
    End If
  End If
End Sub

Private Sub QuitMe()
  App.Quit
End Sub

 

[eforti]

Thanks for the feedback ZVI. The code works as you've designed it but I wonder if it can be done without the msg box? When I click close I'd like to have it just be a one step process as it was in previous versions of excel. The message box prevents me from having to click close twice, but I'm still clicking in two places to get out of excel.

 

[ZVI]

MsgBox is just for testing. In post #4 I’ve mentioned that MsgBox part of the code can be commented.
Like this:

' Put 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
      'If MsgBox("Quit Excel?", vbYesNo + vbQuestion) = vbYes Then
        App.OnTime Now, ThisWorkbook.CodeName & ".QuitMe"
      'End If
    End If
  End If
End Sub

Private Sub QuitMe()
  App.Quit
End Sub

 

[eforti]

Perfect ZVI! Thank you for the help. I misunderstood your point in comment #4. I thought I was commenting on the use of it. Thanks for clarifying.

 

[ZVI]

Glad it helped,
Cheers!

 

[seika7]

Thank you!