OnTime function bugs

[mtsleung]

Hi guys,

I have written a little macro to repeat a task within a certain time in Excel. If I open 2 Excel files in the same time, then so error would happen. Even I close the Excel file with macro, there is still a message asking me whether Enable or Disable the marco...

So I think that must be related to the OnTime function. Can anyone help??

Thanks

Below is my code:

==============
Sub Refresher()

nextrun = Now + TimeValue("00:00:45")
Application.OnTime nextrun, "refresher"
Call URL_Post_Query

End Sub

Sub URL_Post_Query()
...
...
End Sub
==============

 

[erik.van.geit]

Hi, mtsleung,

before you close the file you need to cancel the procedure

here some basic code to adapt to your suits
workbook module

Option Explicit

Private Sub Workbook_Open()
sec = 45
Run "do_something"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "stop_it"
End Sub

normal module

Option Explicit

Public sec As Integer
Public when As Variant

Sub do_something()
when = Now + sec / 60 / 60 / 24
Application.OnTime when, "do_something"
'samplecode writing to sheet
Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Time
End Sub

Private Sub stop_it()
Application.OnTime EarliestTime:=when, Procedure:="do_something", schedule:=False
End Sub

when other workbooks are open: do you want to
cancel the procedure until the workbook is active again ?
then add this to the workbookmodule

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Run "do_something"
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Run "stop_it"
End Sub

run it at the scheduled time
then add workbook references to your procedure
this line would bug

Worksheets("thisone").Range("A1") = 1

this would work

ThisWorkbook.Worksheets("thisone").Range("A1") = 1

kind regards,
Erik

 

[mtsleung]

I see, thanks Erik!!!