Timeout on manual entry in Excel

Question

I want a user to enter data into an Excel cell, but if the entry but doesn't finish in a given time, I want to stop entry at that timeout, enter into the cell what ever has been typed up until that time.

Update

I tried the below code now but this does not work. It does not close the form or timeout.

Sub Button1_Click()

    Dim PauseTime, StartTime
    On Error Resume Next
    UserForm1.Show
    ' Set duration in seconds
    PauseTime = 5  
    ' Set start time                     
    StartTime = Timer                   
    Do While Timer < StartTime + PauseTime
      DoEvents ' Yield to other processes
    Loop
    ActiveCell.Value = UserForm1.TextBox1.Value
    Unload UserForm1 ' close the form

End Sub

VBA doesn't run when a cell is in edit mode. You'd need to give them a userform to type into, or position a textbox over the cell. — Tim Williams, Jan 04 '20 at 05:15
Somewhat similar [question](https://stackoverflow.com/q/43530314/2165759) — omegastripes, Jan 04 '20 at 08:55

score 0 · Accepted Answer · answered Jan 04 '20 at 07:36

0

If you want to use a userform and textbox for this, you can do it this way:

Private Sub UserForm_Activate()
  Dim PauseTime, StartTime

  PauseTime = 5                       ' Set duration in seconds
  StartTime = Timer                   ' Set start time
  Do While Timer < StartTime + PauseTime
    DoEvents                          ' Yield to other processes
  Loop
  ActiveCell.Value = TextBox1.Value   ' set the active cell's value to what is in the textbox
  End                                 ' close the form and end the program

End Sub

_{I do not advise this type of GUI tho, as it will confuse the user.}

answered Jan 04 '20 at 07:36

braX

11,506
5
20
33

I tried the following code, but the UserForm just stays active until manually closed, i..e it does not time out and then close. What am I missing? Sub Button1_Click() Dim PauseTime, StartTime On Error Resume Next UserForm1.Show PauseTime = 5 StartTime = Timer Do While Timer < StartTime + PauseTime DoEvents ' Yield to other processes Loop ActiveCell.Value = UserForm1.TextBox1.Value Unload UserForm1 End Sub – PLL Jan 05 '20 at 23:25
It worked just fine when I tested it. The `End` statement ends all VBA processing, so any forms that are open will automatically close. Try setting a breakpoint (F9) on the `End` line, and it should stop there when it gets there, then press F8 to execute the `End` line. Does the `End` line execute? Do you maybe have some code somewhere that is causing the form to open again? – braX Jan 05 '20 at 23:29
Should this code be associated with the UserForm or the ButtonClick or should it not matter? – PLL Jan 05 '20 at 23:53
If you look at the name of the function, you will see that I put it on the userform, and it runs automatically when the form is displayed (using the Activate event) - Try it all by itself on a test worksheet and test userform. Paste the code, and then run the form manually. Can you get that to work at least? – braX Jan 06 '20 at 02:00

Timeout on manual entry in Excel

1 Answers1