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

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.

CodePudding user response：

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