Write a macro in VBA - succeed AFTER the user has closed Excel

Question

I need to write a macro in Excel VBA that completes the process running in windows tasks AFTER excel has been closed. I tried to do this on the event workbook_BeforeClose

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

Here where MacroCloseProcess is defined as

 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 to the book, excel gives the user the option "Do you want to save the changes made to" Sheet1.xlsx "? Save, not save, cancel

If the user clicks on cancel, Excel does not exit (according to the design), but oh, the process is completed because it was in the "BeforeClose" event. How can I write this code so that it gets after closing Excel?

Answer 1

Take control of the user's decision. This is simple code that could be improved if necessary.

 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 

Answer 2

Some of the other ideas are in order, and I agree with saving in the first place, but you must request their permission in advance. It also allows the user to save first when he first checks to see if he will save.

You must call the user to save the book until it is completed. For instance.

 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 

Answer 3

Perhaps you could offer the user the save option that you want to save before closing excel, and then cancel further dialogs for the user by

Application.DisplayAlerts=False or

ThisWorkbook.Close (False)