如何在excel VBA中检查或取消MULTIPLE等待的application.ontime事件？ [英] How can I check for or cancel MULTIPLE pending application.ontime events in excel VBA?

问题描述

我正在使用Application.Ontime事件从单元格中拉一个时间字段，并计划一个子程序在当时运行。我的Application.Ontime事件在Workbook_BeforeSave事件上运行。因此，如果用户多次使用（更改所需时间+保存工作簿），则会创建多个Application.Ontime事件。理论上我可以用唯一的时间变量跟踪每个事件。但是有没有办法检查/解析/取消挂起的事件？

  Private Sub Workbook_BeforeSave 
 SendTime = Sheets（Email）。Range（B9）
 Application.OnTime SendTimeSendEmail
 End Sub 
 
 Private Sub Workbook_BeforeClose 
 Application.OnTime SendTime，SendEmail，False 
 End Sub 
  

所以如果我：

将B9更改为12:01，保存工作簿

将B9更改为12:03，保存工作簿

更改B9到12:05，保存工作簿

将B9更改为12:07，保存工作簿

etc

I最终会发生多个事件。我只想要一个事件（最近安排的一个）

如何在Workbook_BeforeClose事件上取消所有待处理的事件（或至少列举它们）？ p>

解决方案

或者你可以创建一些单元格（如算盘），例如：

如果我使用application.ontime：

  if range（V1）< 1然后
 
 Application.OnTime dTime，MyMacro
 
 range（V1）= range（V1）+ 1 
 
 end如果
  

如果我想停止计数...

  Application.OnTime dTime，MyMacro，False 
 
 range（V1）= range（V1） -  1 
  

I'm using the Application.Ontime event to pull a time field from a cell, and schedule a subroutine to run at that time. My Application.Ontime event runs on the Workbook_BeforeSave event. As such, if a user (changes the desired time + saves the workbook) multiple times, multiple Application.Ontime events are created. Theoretically I could keep track of each event with a unique time variable.. but is there a way to check/parse/cancel pending events?

Private Sub Workbook_BeforeSave
    SendTime = Sheets("Email").Range("B9")
    Application.OnTime SendTime, "SendEmail"
End Sub

Private Sub Workbook_BeforeClose
    Application.OnTime SendTime, "SendEmail", , False
End Sub

So if I:
change B9 to 12:01, Save the workbook
change B9 to 12:03, Save the workbook
change B9 to 12:05, Save the workbook
change B9 to 12:07, Save the workbook
etc

I end up with multiple events firing. I only want ONE event to fire (the most recently scheduled one)

How can I cancel ALL pending events (or enumerate them at least) on the Workbook_BeforeClose event?

解决方案

or you can just create some cell (like abacus), for example:

if I use application.ontime:

if range("V1") < 1 then

    Application.OnTime dTime, "MyMacro"

    range("V1")=range("V1") + 1

 end if

if I want to stop counting...

   Application.OnTime dTime, "MyMacro", , False

   range("V1")=range("V1") - 1

这篇关于如何在excel VBA中检查或取消MULTIPLE等待的application.ontime事件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！