J
JK
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
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