|
Only Workbook Open?
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 |
Only Workbook Open?
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.... "Otto Moehrbach" wrote in message ... 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 |
Only Workbook Open?
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 Otto Moehrbach wrote: 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 -- Dave Peterson |
Only Workbook Open?
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. "Otto Moehrbach" wrote: 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 |
Only Workbook Open?
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 "Dave Peterson" wrote in message ... 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 Otto Moehrbach wrote: 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 -- Dave Peterson |
Only Workbook Open?
Excel closed for me.
You sure you only have one open workbook. Try adding: msgbox application.workbooks.count to the top of the routine. Otto Moehrbach wrote: 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 "Dave Peterson" wrote in message ... 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 Otto Moehrbach wrote: 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 -- Dave Peterson -- Dave Peterson |
Only Workbook Open?
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" wrote: 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 "Dave Peterson" wrote in message ... 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 Otto Moehrbach wrote: 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 -- Dave Peterson |
Only Workbook Open?
That should be:
ThisWorkbook.Saved = True "Otto Moehrbach" wrote: 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 "Dave Peterson" wrote in message ... 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 Otto Moehrbach wrote: 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 -- Dave Peterson |
Only Workbook Open?
Don't forget the personal.xls is included in the workbook count.
"Otto Moehrbach" wrote: 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 "Dave Peterson" wrote in message ... 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 Otto Moehrbach wrote: 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 -- Dave Peterson |
Only Workbook Open?
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" wrote: Don't forget the personal.xls is included in the workbook count. "Otto Moehrbach" wrote: 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 "Dave Peterson" wrote in message ... 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 Otto Moehrbach wrote: 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 -- Dave Peterson |
| All times are GMT +1. The time now is 11:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com