[Solved] VBA: KILL ONTIME EVENT

  • I have a UserForm that allows the user 5 minutes to either input the required info or cancel the transaction. If they fail to do so, the user form closes and they are returned to a menu screen. The code is as follows:


    Private Sub UserForm_Activate()
    Application.OnTime Now+TimeValue("00.05.00"), "timeup"
    End Sub


    The code works great, perhaps too great. My question.... how do I kill the ontime event if the user cancels the transaction or inputs the required info & clicks on the appropriate control button to move to the next step???


    Everytime the user enters this form (and they may eneter it several times), a new ontime event is established, which I believe is gobbling up memory.


    Any suggestions would be very much appreciated.


    Thanks
    Ted.............

  • Simply call the onTime method again with the same parameters, but set the schedule parameter to FALSE. For your example use this code to set the timer:


    exitTime = Now+TimeValue("00.05.00")
    Application.OnTime exitTime, "timeup"


    and the following line


    Application.OnTime exitTime, "timeup",,False


    to cancel the timer. [Remember to make the ExitTime variable public/shared as necessary depending on where the stop code will be executed]


    HTH, Dzinja

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!