Write a Macro in VBA - excel AFTER the user has closed the Excel

5
votes

I need to write a macro in Excel VBA, that terminates a process running in windows tasks AFTER the excel has been closed down. I tried it doing this on event workbook_BeforeClose

Private Sub Workbook_BeforeClose(CANCEL As Boolean)
    Run "MacroCloseProcess"
End Sub

Where as MacroCloseProcess is defined like this 

Private Sub MacroCloseProcess()
    Dim oWMT As Object, oProcess As Object
    Set oWMT = GetObject("winmgmts://")
    For Each oProcess In oWMT.InstancesOf("Win32_Process")
        If (oProcess.name) = pWcfHostApp Then

            If oProcess.Terminate() = 0 Then Exit Sub
        End If
    Next
End Sub

This works, BUT, if there are changes made in the workbook, excel gives the user option to "Do you want to save the changes you made to 'Sheet1.xlsx' ? Save, Don't Save, Cancel

If user clicks cancel, Excel does not exit ( as per design) but oh, the process has been terminated because it was in a "BeforeClose" event. How can i write this code so that it hits after the excel closes ?

3
vbaexcelms-office

3 Answers

5
votes

Take control of the user decision. This is simple code which could be improved if needed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'take control of what user can do:
If MsgBox("Do you want to save and exit?", vbYesNo) = vbYes Then
    Application.DisplayAlerts = False
        ThisWorkbook.Save
        'call your MacroCloseProcess here
    Application.DisplayAlerts = True
Else
    Cancel = True
End If
End Sub

*EDIT * Better and more elegant option:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'take control of what user can do:
    Application.DisplayAlerts = False
    Dim filePath As Variant

    If Len(ThisWorkbook.Path) > 0 Then
        'has been already saved therefore just ask
        'this would be rarely meet, only whan call for the first time
        'or if this solution was placed in class module for all doc

        If MsgBox("Do you want to save and exit?", vbYesNo) = vbYes Then
            ThisWorkbook.Save
            'call your MacroCloseProcess here
            MsgBox "exit" '<-- to remove, kept for tests
        Else
            Cancel = True
        End If
    Else
        'document was not saved before- show standard file dialog

        filePath = Application.GetSaveAsFilename()
        If VarType(filePath) = vbString Then

            ActiveWorkbook.SaveAs Filename:=filePath
            'call your MacroCloseProcess here
            MsgBox "exit" '<-- to remove, kept for tests

        Else
            Cancel = True
        End If
    End If
    Application.DisplayAlerts = True
End Sub
5
votes

Some of the other ideas are ok and I agree with saving first, but you should ask for their permission beforehand. This also allows the user to save first as it checks first if it's saved.

You should invoke the user to save the workbook before you terminate. E.g.

Private Sub Workbook_BeforeClose(CANCEL As Boolean)

If Not Me.Saved Then 
    Msg = "Do you want to save the changes you made to " 
    Msg = Msg & Me.Name & "?" 
    Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) 
    Select Case Ans
        Case vbYes 
            Me.Save 
        Case vbNo 
            Me.Saved = True 
        Case vbCancel 
            Cancel = True 
            Exit Sub 
    End Select
End If

Run "MacroCloseProcess"

End sub
0
votes

Maybe you could offer the user a do you wish to save option before you close the excel and then suppress any further dialogs for the user by

Application.DisplayAlerts=False or

ThisWorkbook.Close (False)