Application.Quit does not work when ActiveWindow.Visible is False

[Goodwin]

Here's a real head scratcher...Within the BeforeClose event, I call one of two procedures depending on whether the workbook is the only workbook in that particular instance of Excel. The first works fine. The second ends up working exactly like the first despite the use of Application.Quit. So, I end up having to click Excel's (the app's not the wb's) close button twice to get the desired effect. Any explanation would be greatly appreciated.

Sub CloseWorkbook()
ThisWorkbook.Windows(1).Visible = False
ThisWorkbook.Saved = True
ThisWorkbook.Windows(1).Close False
End Sub
 
Sub CloseWorkbookAndExcel()
ThisWorkbook.Windows(1).Visible = False
ThisWorkbook.Saved = True
Application.Quit
End Sub

 

[xenou]

Just offhand, can you close a window? I'd prefer (in whatever variation is required for your purpose):

ThisWorkbook.Close

By habit, I like to specify whether the changes should be saved or not (months later if I come back to this code it helps me to remember if I intended to close with saves or without saves):

ThisWorkbook.Close SaveChanges:=True

Or:

ThisWorkbook.Close SaveChanges:=False

Also, there's no point in setting the saved bit to true and then closing - just close without saving changes.

Edit: Okay, to answer my own question, yes you can close a window to close a workbook. Still, I would prefer to work with the ThisWorkbook object - especially as you are hiding windows it becomes confusing what window you really expect Windows(1) to be ... or what it might actually be. Also, since you are working in a before close event, you can just set the Saved bit to True and let the action continue - if the workbook was closed, it will close, and if the application was exited it will exit (depending on what button or file menu item was chosen or clicked). Again, no point in hiding the window if the workbook won't be saved (the workbook is about to be closed anyway, and you aren't saving the change in the window visibility).

 

[Goodwin]

Thanks for the detailed reply xenou. Re: using the ThisWorkbook object, I agree. In fact, that's how my code originally read. But, as I messed around trying to get this to work, I tried this alternative. It's just where I happened to end up...but, I'm with you on that one.

Your last sentence actually caused a small light bulb to go on in my head. And, I guess herein lies my problem. Is it possible to change the windows().visible property to false and have that "stick" even if the user elects to not save changes?

Furthermore, for whatever reason, it seems like changing the windows().visible property inherently alters the "expected" process. In other words, whether I'd expect the workbook only to close or the workbook and Excel to close (depending on the button clicked), only the workbook closes. This is why I'm at such a loss.

 

[xenou]

Your last sentence actually caused a small light bulb to go on in my head. And, I guess herein lies my problem. Is it possible to change the windows().visible property to false and have that "stick" even if the user elects to not save changes?

I don't think so, strictly speaking - the window visibility must be saved only (as I know) by saving the workbook itself. If the user doesn't want to save then we won't be able to save the visibility either. I'm not sure what to recommend - what kind of workbooks do we have that users are working with them in hidden/visible states at different times?

Furthermore, for whatever reason, it seems like changing the windows().visible property inherently alters the "expected" process. In other words, whether I'd expect the workbook only to close or the workbook and Excel to close (depending on the button clicked), only the workbook closes. This is why I'm at such a loss.

Quitting an app is tricky - it's possible there's other windows open and so on - maybe they need to be dealt with too? I'd prefer to let the user decide whether to quit the app (hit the Exit button, File | Exit, double click Excel icon, etc.) or just close the workbook (Hit the close button, File | Close, etc.). Otherwise, you may need to deal with dialog boxes that might be giving alerts on shutting down.

 

[xenou]

Here's a real head scratcher...Within the BeforeClose event, I call one of two procedures depending on whether the workbook is the only workbook in that particular instance of Excel.

Maybe what's left over is another instance of Excel?

 

[Goodwin]

UPDATE: The code at the bottom appears to work! Thanks for all of your help xenou. I know I've relied on some of your answers before, and I greatly respect your expertise. Your time is much appreciated.

what kind of workbooks do we have that users are working with them in hidden/visible states at different times?

My goals are to hide all of my workbook's windows on close (so that when I re-open my workbook, my custom splash screen is the only thing that's visible; after the splash screen runs, it unhides my workbook's windows); and [ii] to close my workbook and Excel if my workbook is the only workbook open. So, the user's only work with the workbook in its visible state.

Quitting an app is tricky - it's possible there's other windows open and so on - maybe they need to be dealt with too?

Good idea, but I did try that.

Maybe what's left over is another instance of Excel?

Another good idea, but that doesn't seem to be the case.

Here's my updated code, by the way. Same issues...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim response As String
If ThisWorkbook.Saved = False Then
response = MsgBox("Do you want to save the changes you made to " & ThisWorkbook.Name, _
vbExclamation + vbYesNoCancel)
Select Case response
Case vbYes
Windows(ThisWorkbook.Name).Visible = False
ThisWorkbook.Save
Case vbNo
Windows(ThisWorkbook.Name).Visible = False
ThisWorkbook.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
If Application.Workbooks.Count > 1 Then
ThisWorkbook.Close SaveChanges:=False
Else
Application.Quit
End If
End Sub

 

[Jaafar Tribak]

See if this works for you :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
    Application.OnTime Now, Me.CodeName & ".CloseNow"
 
End Sub
 
Private Sub CloseNow()
 
    Call CloseWorkbookAndExcel
 
End Sub

 

[xenou]

Just offhand musing but how can we quit the app if we've closed the workbook that has the code that quits the app? Are you putting this same code in many (two or more) workbooks?

 

[Goodwin]

Just offhand musing but how can we quit the app if we've closed the workbook that has the code that quits the app? Are you putting this same code in many (two or more) workbooks?

I was just about to re-post because, while I thought my posted code was working in all situations, it turns out I was wrong. All this back and forth, though, has clarified (somewhat) for me the underlying problem. I think I can boil it down to this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Select Case Application.Workbooks.Count
      Case Is > 1
         ThisWorkbook.Saved = True
         ThisWorkbook.Close SaveChanges:=False
      Case Is = 1
         ThisWorkbook.Saved = True
         Application.Quit
   End Select
End Sub

If I place this code in the BeforeClose event, everything works as expected. However, once I introduce the Windows(...).Visible statement (see below), my "issue" arises. When I click the workbook's close button on the upper-right portion of the screen, this works as expected. But, when I click Excel's close button, it seems that the Select statement doesn't run...so Excel doesn't close (and obviousbly neither does the workbook). Thank you so much for your patience and help.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Windows(ThisWorkbook.Name).Visible = False
   Select Case Application.Workbooks.Count
      Case Is > 1
         ThisWorkbook.Saved = True
         ThisWorkbook.Close SaveChanges:=False
      Case Is = 1
         ThisWorkbook.Saved = True
         Application.Quit
   End Select
End Sub

 

[Goodwin]

Jaafar...didn't mean to ignore your suggestion. I tried employing OnTime, and it didn't seem to make a difference. Hopefully you have a chance to read my latest post (Post #9) detailing where I stand now. Bottom line, I know I'm being a little picky (in fact, in just came to my attention that Excel 2007 essentially acts the way I'm trying to avoid), but with how much time I've already invested in this, I can't let it go.