• 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.


  • 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!