Code goes into the user form's code section, not the workbook itself. For
the examples, the user form's name is UserForm1
1st method: if no entry made for 10 seconds, form closes, but any entry made
causes it to remain open no matter how long it takes to finish entries.
Option Explicit
Public myUserFormFlag As Boolean
Private Sub UserForm_Activate()
Dim startTimer As Long
myUserFormFlag = False
startTimer = Timer
Do While myUserFormFlag = False
DoEvents
If Timer > (startTimer + 10) Then
UserForm1.Hide
Exit Do
End If
Loop
End Sub
Private Sub ComboBox1_Change()
myUserFormFlag = True
End Sub
Private Sub TextBox1_Change()
myUserFormFlag = True
End Sub
The 'secret' in that is that you need to add the line
myUserFormFlag = True
at the start of any action event for any of your controls on the form. Then
when any one of them is used, the timing tests stop and the form stays open.
A second method: this one will keep the form open as long as someone does
something, as type a letter into a text box, at least once each 10 seconds,
but once no action has taken place on the form at all for 10 seconds, it
closes, even with partially completed text entries:
Option Explicit
Public startTimer As Long
Private Sub UserForm_Activate()
startTimer = Timer
Do While Timer < (startTimer + 10)
DoEvents
Loop
UserForm1.Hide
End Sub
Private Sub ComboBox1_Change()
startTimer = Timer
End Sub
Private Sub TextBox1_Change()
startTimer = Timer
End Sub
With this second one, the 'secret' is in resetting startTimer when any
control is changed/used on the form. So the 10-second clock gets restarted
any time there is activity on the form.
I showed how to do it with a form with a ComboBox and a TextBox on it, just
do much the same for any/all controls on the form and you should be fine.
