CODEWITHSUNDEEP
excelvba
Smartbuild
Smartbuild

Reputation: 119

VBA: Stop recursive Function re-opening workbook after workbook close

I'm using the following code to check if the read-only workbook a user is viewing has been updated:

Function NewVersion() As Boolean

    If a <> b Then Flag = True: Exit Function

    Application.OnTime Now + TimeValue("00:00:05"), "NewVersion"

End Function

However, whenever I close the workbook, it reopens (presumably as NewVersion is loaded into memory and set to run in 5sec?)

How can I prevent this happening?

I've tried a variety of things but no luck so far!

Upvotes: 1

Views: 123

Answers (1)

user6432984
user6432984

Reputation:

Create a public variable to keep track of the next time that your macro should fire and cancel if using the Schedule parameter of Application.OnTime.

Public ScheduledTime As Date

Function NewVersion() As Boolean
    ScheduledTime = Now + TimeValue("00:00:05")

    Application.OnTime ScheduledTime, "NewVersion"

End Function

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   On Error Resume Next
   Application.OnTime ScheduledTime, "NewVersion", Schedule:=False
End Sub

Upvotes: 1

Related Questions