none
How to close Excel in VBA for Excel RRS feed

  • Question

  • I am developing a project in VBA for Excel which will allow the user to add data to or edit data in various sheet in the workbook which contains the VBA project.

    The project starts (when a Workbook Activate event occurs) and opens a User Form (named Menu) which has buttons to allow the user to perform various operations. An additional button allows the user signal that he/she is finished working on the workbook.

    This last button closes all of the windows in the Excel application, closes the workbook, and then calls Application.Quit.

    Public updated As Boolean

    Private Sub cmdClose_Click()
        Dim wnd As Window
        
        Hide
        For Each wnd In ThisWorkbook.Windows
            wnd.Close (updated)
        Next wnd
        
        ThisWorkbook.Close (updated)
        
        Application.Quit
    End Sub

    Assuming that this workbook is the only workbook open at this time, I expect that Excel will close completely. Instead, the worksheets disappear but Excel remains on the screen with just a light gray background. The user must click the close button (X) to make this go away.

    Is there anything that I need to add to the above code to make Excel close completely?

    Thanks


    Norm

    Sunday, January 5, 2020 9:45 PM

Answers

  • The code stops running when the workbook is closed, so the line Application.Quit is not executed.

    Instead of

        ThisWorkbook.Close (updated)

    save the workbook if required.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Norm Bullen Monday, January 6, 2020 6:32 PM
    Sunday, January 5, 2020 10:29 PM

All replies

  • The code stops running when the workbook is closed, so the line Application.Quit is not executed.

    Instead of

        ThisWorkbook.Close (updated)

    save the workbook if required.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Norm Bullen Monday, January 6, 2020 6:32 PM
    Sunday, January 5, 2020 10:29 PM
  • The workbook is closed before it gets to Application.Quit so the command is never processed.

    I assume that the variable "Public updated As Boolean" is set to true when changes are made to the workbook. Actually there is a built in function to establish if changes have been made. The following code tells the user if there are unsaved changes and depending on the response from the user, the workbook will be saved and Quit Excel or Quit Excel without saving.

    Closing the workbook occurs automatically when the quitting the application.

    You could edit the code to use your "updated" variable in lieu of the built in Saved function.

    Private Sub cmdClose_Click()               
        Dim msgResponse As Variant

        If ThisWorkbook.Saved = False Then
            msgResponse = MsgBox("There are unsaved changes." & vbCrLf _
                                & "Do you want to save before quitting?", vbYesNo)
            If msgResponse = vbYes Then
                ThisWorkbook.Save
            Else
                Application.DisplayAlerts = False   'Suppress alert re unsaved changes when quitting
            End If
        End If
        Application.Quit
    End Sub

    Regards, OssieMac

    Sunday, January 5, 2020 10:33 PM