|
Help with Proper Closing Procedure
I would like to have my users have multiple WBs open (i.e., abc.xls,
xyz.xls). But I'm struggling to write a good closing procedure. If abc.xls is closed or terminated, I want xyz.xls to remain open. Moreover, if only one WB is open then I want to shut down Excel (Application.Quit) when it's closed or terminated. Here's the procedure I'm using (which works for one open WB but not multiple open WBs). I would very much appreciate help. Thank you. [ThisWorkbook] Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Config As String Dim Ans As String If ActiveWorkbook.Saved = False Then ActiveWorkbook.Saved = True Config = vbYesNo + vbQuestion + vbDefaultButton1 Ans = MsgBox("Would you like to save your changes to " & ActiveWorkbook.Name & "? ", Config, "ProAPOD Alert") If Ans = vbYes Then Call RestoreToolbars2 Else: Call RestoreToolbars End If ElseIf ActiveWorkbook.Saved = True Then Call RestoreToolbars End If End Sub [WorkbookClose] Option Explicit Sub RestoreToolbars() 'WHEN NOT SAVE CHANGES AT CLOSING Dim Cell As Range On Error Resume Next For Each Cell In Sheets("HideAll").Range("A1:A20") _ .SpecialCells(xlCellTypeConstants) CommandBars(Cell.value).Visible = True Next Cell Call RestoreFormulaBar End Sub Sub RestoreFormulaBar() 'WHEN NOT SAVE CHANGES AT CLOSING If Sheets("HideAll").Range("B1").value = "Yes" Then Application.DisplayFormulaBar = True Else: Application.DisplayFormulaBar = False End If Application.DisplayAlerts = False Application.Quit End Sub Sub RestoreToolbars2() 'WHEN SAVE CHANGES AT CLOSING Dim Cell As Range On Error Resume Next For Each Cell In Sheets("HideAll").Range("A1:A20") _ .SpecialCells(xlCellTypeConstants) CommandBars(Cell.value).Visible = True Next Cell Call RestoreFormulaBar2 End Sub Sub RestoreFormulaBar2() 'WHEN SAVE CHANGES AT CLOSING If Sheets("HideAll").Range("B1").value = "Yes" Then Application.DisplayFormulaBar = True Else: Application.DisplayFormulaBar = False End If ActiveWorkbook.Save Application.DisplayAlerts = False Application.Quit End Sub Option Explicit |
Help with Proper Closing Procedure
Well atleast this may answer one of your questions
your code for saving any open book If Workbooks.Count = 1 Then Application.Quit best N10 "JK" wrote in message news:Ws_Pf.24133$G%2.6114@trnddc07... I would like to have my users have multiple WBs open (i.e., abc.xls, xyz.xls). But I'm struggling to write a good closing procedure. If abc.xls is closed or terminated, I want xyz.xls to remain open. Moreover, if only one WB is open then I want to shut down Excel (Application.Quit) when it's closed or terminated. Here's the procedure I'm using (which works for one open WB but not multiple open WBs). I would very much appreciate help. Thank you. [ThisWorkbook] Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Config As String Dim Ans As String If ActiveWorkbook.Saved = False Then ActiveWorkbook.Saved = True Config = vbYesNo + vbQuestion + vbDefaultButton1 Ans = MsgBox("Would you like to save your changes to " & ActiveWorkbook.Name & "? ", Config, "ProAPOD Alert") If Ans = vbYes Then Call RestoreToolbars2 Else: Call RestoreToolbars End If ElseIf ActiveWorkbook.Saved = True Then Call RestoreToolbars End If End Sub [WorkbookClose] Option Explicit Sub RestoreToolbars() 'WHEN NOT SAVE CHANGES AT CLOSING Dim Cell As Range On Error Resume Next For Each Cell In Sheets("HideAll").Range("A1:A20") _ .SpecialCells(xlCellTypeConstants) CommandBars(Cell.value).Visible = True Next Cell Call RestoreFormulaBar End Sub Sub RestoreFormulaBar() 'WHEN NOT SAVE CHANGES AT CLOSING If Sheets("HideAll").Range("B1").value = "Yes" Then Application.DisplayFormulaBar = True Else: Application.DisplayFormulaBar = False End If Application.DisplayAlerts = False Application.Quit End Sub Sub RestoreToolbars2() 'WHEN SAVE CHANGES AT CLOSING Dim Cell As Range On Error Resume Next For Each Cell In Sheets("HideAll").Range("A1:A20") _ .SpecialCells(xlCellTypeConstants) CommandBars(Cell.value).Visible = True Next Cell Call RestoreFormulaBar2 End Sub Sub RestoreFormulaBar2() 'WHEN SAVE CHANGES AT CLOSING If Sheets("HideAll").Range("B1").value = "Yes" Then Application.DisplayFormulaBar = True Else: Application.DisplayFormulaBar = False End If ActiveWorkbook.Save Application.DisplayAlerts = False Application.Quit End Sub Option Explicit |
| All times are GMT +1. The time now is 01:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com