Only Workbook Open?

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

Otto Moehrbach

Excel XP & Win XP
I'm helping an OP and he has come up with an interesting requirement
that I have never run into.
Using the Workbook_BeforeClose event macro, he wants to close only the
active workbook if there are other workbooks open. However, if the active
workbook is the only open workbook, he wants to close the application
(Excel) as well. The tricky part is that the code for this is in the active
workbook. Once the active workbook closes, the code stops running and
cannot check for an open workbook.
The only solution I see is to check to see if more than one workbook is
open BEFORE closing the active workbook.
My first question: How can I check to see if more than one workbook is
open?
My second question: How can I code to close Excel AFTER the workbook
containing the code is closed?
Thanks for your time. Otto
 
Hi,
Try the below code.
It will check to see if any other WorkBooks are open, if there is a another open then they will
remain open and the ActiveWork book will close.
If there is No other WB opened, then Excel quits.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Dim WB As Workbook
For Each WB In Application.Workbooks
Debug.Print WB.Name
If WB.Name <> ThisWorkbook.Name Then
If WB.Windows(1).Visible = True Then Exit Sub
End If
Next
Sheets("Sheet1").Select
Application.Quit
End Sub


Might help you out

Corey....

Excel XP & Win XP
I'm helping an OP and he has come up with an interesting requirement
that I have never run into.
Using the Workbook_BeforeClose event macro, he wants to close only the
active workbook if there are other workbooks open. However, if the active
workbook is the only open workbook, he wants to close the application
(Excel) as well. The tricky part is that the code for this is in the active
workbook. Once the active workbook closes, the code stops running and
cannot check for an open workbook.
The only solution I see is to check to see if more than one workbook is
open BEFORE closing the active workbook.
My first question: How can I check to see if more than one workbook is
open?
My second question: How can I code to close Excel AFTER the workbook
containing the code is closed?
Thanks for your time. Otto
 
How about:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Application.Workbooks.Count > 1 Then
'just let the workbook close
Else
Application.Quit
End If

End Sub
 
Sub wbks()
x = Workbooks.Count
MsgBox x
'Gives number or workbooks
ThisWorkbook.Saved = True
Application.Quit
'closes the active workbook and Excel. If you have other wb open, Excel
won't close.
 
Dave
Maybe I misunderstood your code, but I used the following exactly as
written. The result, if only the one workbook is open, is that the WB
closes and Excel just sits there, open.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.Workbooks.Count > 1 Then
'just let the workbook close
Else
Application.Quit
End If
End Sub
 
Excel closed for me.

You sure you only have one open workbook.

Try adding:

msgbox application.workbooks.count

to the top of the routine.
 
Try it another way:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.Workbooks.Count < 2 Then
ActiveWorkbook.Saved = True
Application.Quit
End If
End Sub


Otto Moehrbach said:
Dave
Maybe I misunderstood your code, but I used the following exactly as
written. The result, if only the one workbook is open, is that the WB
closes and Excel just sits there, open.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.Workbooks.Count > 1 Then
'just let the workbook close
Else
Application.Quit
End If
End Sub
Click to expand...
 
That should be:

ThisWorkbook.Saved = True

Otto Moehrbach said:
Dave
Maybe I misunderstood your code, but I used the following exactly as
written. The result, if only the one workbook is open, is that the WB
closes and Excel just sits there, open.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.Workbooks.Count > 1 Then
'just let the workbook close
Else
Application.Quit
End If
End Sub
Click to expand...
 
Don't forget the personal.xls is included in the workbook count.

Otto Moehrbach said:
Dave
Maybe I misunderstood your code, but I used the following exactly as
written. The result, if only the one workbook is open, is that the WB
closes and Excel just sits there, open.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.Workbooks.Count > 1 Then
'just let the workbook close
Else
Application.Quit
End If
End Sub
Click to expand...
 
Yes, I meant to mention that any hidden workbooks will be in the count, so if
Excel is not closing when you think you only have one wb open, then step
through the code and see how many shows with the Workbooks.Count and if it is
more than 1, you have a hidden wb somewhere.

JMB said:
Don't forget the personal.xls is included in the workbook count.
Click to expand...
 
Back
Top