How to set a timer in a UserForm Textbox?

Question

I have a code that works perfectly but I have to click on the command button to activate it.

What I want is for the timer to run automaticaly when I open the workboox/UserForm.

When the timer reach 0 seconds, I want the workbook to automatically close using

Workbooks("OUTIL_CRN.xlsm").Save
Workbooks("OUTIL_CRN.xlsm").Close

This is the code that works with a commmand button:

In a module:

Public Const AllowedTime As Double = 1

In the Userform:

Private Sub CommandButton1_Click()

Dim userClickedPause As Boolean ' Gets set to True by the Pause button

Dim stopTime As Date

    userClickedPause = False
    ' If AllowedTime is the number of minutes with a decimal part:
    stopTime = DateAdd("s", Int(AllowedTime * 600), Now) ' add seconds to current time

    ' If AllowedTime is the number of seconds:
    'stopTime = DateAdd("s", AllowedTime, Now) ' add seconds to current time
    Do
        With UserForm1.TextBox1
            .Value = Format(stopTime - Now, "Nn:Ss")
        End With
        DoEvents
        If userClickedPause = True Then
            Exit Do
        End If
    Loop Until Now >= stopTime


End Sub
Private Sub CommandButton2_Click()
    userClickedPause = True
End Sub

Avoid referring to `UserForm1` in the code-behind of `UserForm1`: that will not work as you expect, the minute you start `New`-ing up your forms. Use `Me` to refer to the *current instance* of the form. That said [this code might interest you](https://codereview.stackexchange.com/q/229656/23788) — Mathieu Guindon, Dec 08 '19 at 02:38
@MathieuGuindon thank you for your reply. I took a look at your link, but as a not so experienced vba user, I don't know where to look and what to understand. This link isn't much of help to me. — Chadi N, Dec 08 '19 at 02:45
You need to learn to walk before you can run then. There are much easier ways to learn some VBA than dealing with timers and asynchronous callbacks. This busy-looping is hijacking the form's own message loop, ...everything is holding on a house of cards. — Mathieu Guindon, Dec 08 '19 at 02:48
@MathieuGuindon If I had the choice, Id take it more easy and indeed learn to walk before I can run... but it's for work. I need to be able to time the userform for the users because they often forget the workbook opened, making it impossible for other users to use the Userform. — Chadi N, Dec 08 '19 at 02:51
See [this Analyst Cave article](https://analystcave.com/excel-vba-application-ontime/) (I've no affiliation). — Mathieu Guindon, Dec 08 '19 at 02:57

score 1 · Answer 1 · answered Dec 08 '19 at 02:51

A lot depends on how you're showing the form. If you have this:

UserForm1.Show

Then the form is modal, the default instance is shown, and you could handle the Activate event to execute code as soon as the dialog becomes active.

If you have this:

UserForm1.Show vbModeless

Then the form isn't modal, and the Activate event will be a problem, because the timer will then re-initialize whenever the user clicks on the form after deactivating it by clicking somewhere else on the screen.

If you have this:

With New UserForm1
    .Show
End With

Then the TextBox1.Text property isn't being assigned on the instance that's being shown, and the box remains empty. However with that we now fully control when the form object gets created and destroyed, and now we can handle the Initialize event (fired once per instance, when the object is first created) because we know that the form object has just been created... but then, the form won't ever be shown, because the only available thread is busy with that Do...Loop, and while it allows the dialog's message pump to pump events (thanks to DoEvents), if the form isn't shown by the time that loop is entered, then no other code gets to run - so, firing that loop in an Initialize handler would be a very bad idea.

Consider looking into leveraging Application.OnTime to schedule the execution of a procedure: what you want to do, is to say "invoke DoStuff in 1 second", and then resume executing whatever - not busy-looping, rather letting the form's message loop do its job. See this post for one of many examples.

Since the timer display might be a little tricky for my limited knowledge. Can you please help me on how to automatically close the workbook using `Workbooks("OUTIL_CRN.xlsm").Close` and `Application.OnTime` — Chadi N, Dec 08 '19 at 03:30
Does your macro open this `OUTIL_CRN.xlsm` file? If so then you should have a reference to the `Workbook` object returned by `Workbooks.Open`; store that reference in a module-level variable, and then your `OnTime` callback can use it. Make sure you're in a standard module (not a worksheet or ThisWorkbook module), see how it goes =) — Mathieu Guindon, Dec 08 '19 at 03:36
` Public Sub fermeoutil() Workbooks("OUTIL_CRN.xlsm").Close Call test End Sub Sub test() Application.OnTime Now + TimeValue("00:00:10"), "fermeoutil" End Sub ` not working ... — Chadi N, Dec 08 '19 at 04:02
Is that code in a standard module (e.g. `Module1`)? That said I don't understand why you would want to close a workbook every 10 seconds... you'll need to handle the error that will be raised when `Workbooks("...")` can't find the workbook 10 seconds after you first closed it. Does "not working" stand for "subscript out of range" error 9? — Mathieu Guindon, Dec 08 '19 at 04:15
I dont want it to close every 10 seconds. I'm just trying it out. Yes, it is in a module (module1). Not working stands for nothing happening at all at 10 seconds mark! — Chadi N, Dec 08 '19 at 04:32

How to set a timer in a UserForm Textbox?

1 Answers1