application.ontime没有取消或在后台运行 [英] application.ontime not cancelling or running in background

问题描述

我使用Application.Ontime命令在一段时间不活动(10分钟)后自动关闭电子表格.

I'm using the Application.Ontime command to automatically close a spreadsheet after a period of inactivity (10 minutes).

以下代码似乎可以正常运行，但是，如果您自己手动关闭工作表，则工作簿似乎仍在后台处于活动状态，并且在最后一个指定的结束时间"将自行打开，以便可以关闭自己.

The following code seems to work in general, however, it appears that if you manually close the sheet yourself, the workbook still seems to be active in the background and at the last designated 'endtime' will open itself so that it can close itself.

这在VBA代码窗口中也很明显，因为在CloseWB宏运行并且excel工作簿似乎已关闭之后，它仍列在VBA项目浏览器窗口中.

This is also evident in the VBA code window as after the CloseWB macro runs and the excel workbook appears to be closed, it is still listed in the VBA project explorer window.

Sub RunTime()
Static EndTime
If Not EndTime = "" Then ActiveWorkbook.Application.OnTime EndTime, "CloseWB", , False
EndTime = Now + TimeValue("00:10:00")
ActiveWorkbook.Application.OnTime EndTime, "CloseWB", , True
End Sub

Sub CloseWB()

    Application.DisplayAlerts = False
    With ThisWorkbook
        .Save
        .Close
    End With
End Sub

我不想完全关闭excel(application.quit)，以防用户打开其他工作簿，但需要尝试停止在后台运行的特定工作簿.

I don't want to completely shutdown excel (application.quit) in case users have other workbooks open but need to try and stop the specific workbook running in the background.

有什么想法吗?

推荐答案

您需要停止计时器.将 EndTime 声明为公共变量，然后在 Workbook_BeforeClose 事件中关闭计时器.

You need to stop the timer. Declare EndTime as a public variable, then turn the timer off in the Workbook_BeforeClose event.

Option Explicit

Public EndTime As Variant

Sub RunTime()
If Not EndTime = "" Then ActiveWorkbook.Application.OnTime EndTime, "CloseWB", , False
EndTime = Now + TimeValue("00:10:00")
ActiveWorkbook.Application.OnTime EndTime, "CloseWB", , True
End Sub

Sub CloseWB()

    Application.DisplayAlerts = False
    With ThisWorkbook
        .Save
        .Close
    End With
End Sub

在工作簿对象中:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnTime earliesttime:=EndTime, procedure:="CloseWB", schedule:=False
End Sub

这篇关于application.ontime没有取消或在后台运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！