如何检测工作簿何时关闭? [英] How to detect when a workbook is closing?

问题描述

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

The Workbook.BeforeClose event triggers when the workbook is about to close but before the saving message prompt which allows cancelling it.

我如何检测工作簿何时已经关闭，可以取消工作簿而不用自定义消息删除或替换保存消息?

How can I detect when the workbook is already closing past the point where it can be cancelled without removing nor replacing the saving message with a custom one?

我发现在线的一种解决方法是将事件与 Workbook.Deactivate事件一起使用看起来像这样:

One workaround I have found online is to use the event together with the Workbook.Deactivate event which looks like this:

工作簿中的代码:

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 (由

One very specific edge case where it triggers incorrectly is if you click to close the workbook and in less than one second close the saving message (press Esc to do it fast enough) and change to another workbook (Alt + Tab) it triggers the Deactivate event with the closing_event condition variable still set to True because disable_closing_event has still not set it to False (scheduled by Application.OnTime for when one second goes by).

我想找到一种解决方案，它不算是一种替代方法，并且可以在这种情况下正常工作.

I would like to find a solution that isn't so much of a workaround and that works correctly against that edge case.

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

The accepted answer has the best solution in my opinion out of all the current answers. I have modified it for my needs and preference to the following code in the workbook:

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

推荐答案

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

This is an evolution of my 1st Answer - it detects the edge case problem by comparing the ActiveWindow.Caption against ThisWorkbook.Name so it can detect that issue and deal with it. It's not the most elegant solution but I believe it works.

工作簿中的所有代码大部分都处于停用状态

All Code in the Workbook most of it in 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种可能的组合进行了测试

In response to comment about saving I tested this for 7 possible combinations as follows

 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        

这篇关于如何检测工作簿何时关闭?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！