Help with Proper Closing Procedure

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
 
N

N10

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top