excel - 如何检测工作簿何时关闭？

Question

Workbook.BeforeClose event当工作簿即将关闭但在允许取消它的保存消息提示之前触发。

如何检测工作簿何时已关闭超过可以取消的点，而无需删除或用自定义消息替换保存消息？

我找到了一个解决方法 online是将事件与 Workbook.Deactivate event 一起使用看起来像这样:

工作簿中的代码:

Private Sub Workbook_BeforeClose(ByRef Cancel As Boolean)

  closing_event = True
  check_time = VBA.Now + VBA.TimeSerial(Hour:=0, Minute:=0, Second:=1)
  Excel.Application.OnTime EarliestTime:=check_time, Procedure:="disable_closing_event"

End Sub

Private Sub Workbook_Deactivate()

  If closing_event Then
    VBA.MsgBox Prompt:="Closing event."
    Excel.Application.OnTime EarliestTime:=check_time, Procedure:="disable_closing_event", Schedule:=False
  End If

End Sub

模块中的代码:

Public closing_event As Boolean
Public check_time As Date

Public Sub disable_closing_event()

  closing_event = False

End Sub

一个非常特殊的边缘情况，它会错误地触发，如果您单击关闭工作簿并在不到一秒的时间内关闭保存消息(按 Esc 足够快地执行此操作)并更改为另一个工作簿 (Alt + Tab) 会触发 Deactivate 事件，且 opening_event 条件变量仍设置为 True 因为 disable_ending_event 仍未将其设置为 False(由 Application.OnTime 安排在一秒过去时)。

我希望找到一种解决方案，该解决方案不是一种解决方法，并且可以针对该边缘情况正确工作。

编辑:

accepted answer在我看来，在所有当前答案中，有最好的解决方案。我根据自己的需要和偏好对工作簿中的以下代码进行了修改:

Private WorkbookClosing As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  WorkbookClosing = True
End Sub

Private Sub Workbook_Deactivate()
  If WorkbookClosing And ThisWorkbook.Name = ActiveWindow.Caption Then
    Workbook_Closing
  Else
    WorkbookClosing = False
  End If
End Sub

Private Sub Workbook_Closing()
  MsgBox "Workbook_Closing event."
End Sub

Answer 1

这是我的第一个答案的演变 - 它通过将 ActiveWindow.Caption 与 ThisWorkbook.Name 进行比较来检测边缘情况问题，以便它可以检测该问题并处理它。这不是最优雅的解决方案，但我相信它是有效的。

工作簿中的所有代码大部分都在 DeActivate 中

Public ByeBye As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ByeBye = "B4C"
End Sub

Private Sub Workbook_Deactivate()
   If ByeBye = "B4C" Then
      If ActiveWindow.Caption = ThisWorkbook.Name Then
         If ThisWorkbook.Saved Then
            MsgBox "No problem - Closing after Saving"
         Else
            MsgBox "No problem - Closing without Saving"
         End If
      Else
         If ThisWorkbook.Saved Then
            MsgBox "No problem - New Workbook Activation"
         Else
            MsgBox "Oops Try Again You Cannot Activate '" & ActiveWindow.Caption & "' until '" & ThisWorkbook.Name & "' has completed processing & IT HAS NOW COMPLETED", vbOKOnly, "Hiding"
            ThisWorkbook.Activate
         End If
      End If
   Else
      MsgBox "No problem - Just Hiding"
   End If
   ByeBye = "Done"
End Sub

Private Sub Workbook_Open()
   ByeBye = "OPENED"
End Sub

为了回应有关保存的评论，我测试了 7 种可能的组合，如下

 1) Closing without Edits - No Saving Involved ... MsgBox Prompted with ... No problem - Closing after Saving       
 2) Not closing - Just Switch Workbook - Whether Edited or Not ... MsgBox Prompted with ... No problem - Just Hiding        
 3) Not closing - Switch Workbook - After Edit & Cancel ... MsgBox Prompted with ... Oops Try Again …       
 4) Closing and saving ... MsgBox Prompted with ... No problem - Closing after Saving       
 5) Closing and Saving after a prior Cancel ... MsgBox Prompted with ... No problem - Closing after Saving      
 6) Closing but Not Saving ... MsgBox Prompted with ... No problem - Closing without Saving         
 7) Closing but not Saving after a prior Cancel ... MsgBox Prompted with ... No problem - Closing without Saving