Use VBA to disable Close (X) and Require a Button to Close and Save

Question

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If wrkBkClose = False Then
MsgBox ("Please Use The Save & Close Button"), vbInformation
Cancel = Not wrkBkClose
End If
End Sub

Workbook BeforeClose Event

Sub CloseSave()
Application.EnableEvents = False
Application.DisplayAlerts = False

If Application.Workbooks.Count = 1 Then
    wrkBkClose = True
    ActiveWorkbook.Close SaveChanges:=True
    Application.Quit
Else
    With ActiveWorkbook
        .Close SaveChanges:=True
    End With
End If

End Sub

Module under a button

The above code works as expected, except the Excel application does not close completely. The following is still left open:

Could someone please help me understand why the application will not completely close? Or is there another way I should be going about performing this procedure. Thanks in advance for the help.

You didn't explain where `wrkBkClose` is declared. If `CloseSave` is in `Module1` then e.g. in the same module, after `Option Explicit`, you need something like `Public wrkBkClose As Boolean`. Also, note that hidden workbooks are also counted. — VBasic2008, Mar 01 '22 at 05:25
My apologies, I did include Public wrkBkClose As Boolean in the module, I just copy and pasted below that accidentally. Thank you for pointing this out. — SouthernGentlemen, Mar 01 '22 at 05:32
You cannot quit if the workbook was closed before. The trick is to just save the workbook without closing and then quit: `ActiveWorkbook.Save : Application.Quit`. [More insights here](https://stackoverflow.com/q/3628252). — VBasic2008, Mar 01 '22 at 06:00

score 1 · Accepted Answer · answered Mar 01 '22 at 06:17

Close Workbook Only via Button

If it is the only open workbook, it will also quit Excel.

ThisWorkbook

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If wrkBkClose = False Then
        MsgBox ("Please Use The Save & Close Button"), vbExclamation
        Cancel = True
    End If
End Sub

Module1

Option Explicit

Public wrkBkClose As Boolean

Sub CloseSave()
    wrkBkClose = True
    If Application.Workbooks.Count = 1 Then
        ThisWorkbook.Save
        Application.Quit
    Else
        ThisWorkbook.Close SaveChanges:=True
    End If
End Sub

Use VBA to disable Close (X) and Require a Button to Close and Save

1 Answers1

Close Workbook Only via Button