时间戳

首页 > 解决方案 > 如何检测工作簿何时关闭？

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

问题描述

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

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

我在网上找到的一种解决方法是将该事件与Workbook.Deactivate 事件一起使用，如下所示：

工作簿中的代码：

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 事件closing_event条件变量仍设置为，True因为disable_closing_event尚未将其设置为False（由Application.OnTime安排在一秒钟后）。

我想找到一个解决方案，它不是一种解决方法，并且可以在这种边缘情况下正常工作。

编辑：

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

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

标签： excelvba

解决方案

这是我的第一个答案的演变 - 它通过将 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

推荐阅读