Alternative to Application.Ontime

[KevinPhilips]

Hi everyone,

I received an Excel VBA Project which links from a button to a IE Object. On this website you have to log in with an account-name and password. There are about 20 users (account-name and password combinations) saved in an Access database. A third column indicates if the user login is being used or not (via a "x" or no "x"). When you click the above mentioned button, the Excel macro seeks for a row that does not have an "x" behind the user login. Then it collects it and gives it to the IE.Object.
So far so good... Now the code triggers an Application.Ontime Event (for 5 minutes passing from NOW) to log out and remove the "x" behind the user login in the access database.

Problem: Of course, when you close Excel before these 5 minutes are passed, the Ontime Event is never triggered. Therefore the "x" stays. That meaning, after this happens 20 times there are no free user logins to choose from.

Do you guys have alternatives for Application.Ontime or even a fix in mind since this whole scenario is not ideal.


Hope you guys can understand my broken English :) Unfortunately, it does not make sense to post code because it is spread throughout 5 modules and 10 different Subs/Functions.

Thank you guys so much in advance.
Kevin

[Doug Robbins]

How about using the Workbook_BeforeClose event to run the same code as your Application.Ontime event. To avoid an error, you might want to have the Application.OnTime event set a Boolean variable the state of which would be checked by the Workbook_BeforeClose event to determine if the code needs to be run.

[ZVI]

There can be two minor issues with usage of events code in ThisWorkbook module.
1. If Application.EnableEvents = False before the workbook loading then Workbook_Open will not run and Application.OnTime is not charged.
2. User after the editing can try to close workbook but choose Cancel in "Save Changes?" dialog.
In this case the code of Workbook_BeforeClose runs with removing "X" in Access database but workbooks stays unclosed.

The below code eliminates these problems.
Copy & paste it into Module1 (not in ThisWorkbook module)


' Put all the code into standard module
Option Explicit
Dim dt As Date

Private Sub Auto_Open()
dt = Now + TimeSerial(0, 5, 0)
Application.OnTime EarliestTime:=dt, Procedure:="MyRemoveX"
End Sub

Private Sub Auto_Close()
MyRemoveX
If dt <> 0 Then
dt = 0
Application.OnTime EarliestTime:=dt, Procedure:="MyRemoveX", Schedule:=False
End If
End Sub

Private Sub MyRemoveX()
MsgBox "The edit time is gone, " & CreateObject("WScript.Network").UserName
' Your code for Access database updating is here
' ...
ThisWorkbook.Close
End Sub

[ZVI]

The fixed part:

Private Sub Auto_Close()
MyRemoveX
If dt <> 0 Then
Application.OnTime EarliestTime:=dt, Procedure:="MyRemoveX", Schedule:=False
dt = 0 ' <-- This should not be before the above line
End If
End Sub