Time-out for InputBox

D

daniel chen

Sub InputBoxTimeout()
' How do I add a time-out conponent to this? Someone please
Range("A1") = InputBox(prompt:=" Enter a number within 1 minute," & vbLf & "
otherwise, 4 will be assumed.")
End Sub
 
B

Bob Phillips

Best way is to have a little userform that you load with a textbox for
input. In the userfor activate, add this code

Application.Ontime Now + TimeSerial(0,1,0),"KillForm"

and then have a sub in a standard code mode

Public Sub KillForm()
Unload Userform1
End SUb

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

daniel chen

Hi Bob
My macro should look more like this.
Sub InputBoxTimeout()
Dim ans As Variant
ans = ""
' How do I add a time-out conponent to this? Someone please
ans = InputBox(prompt:=" Enter a number within 1 minute," & vbLf & _
" otherwise, 4 will be assumed.")
If ans = "" Then Range("A1") = 4
If ans <> "" Then Range("A1") = ans
End Sub

I have never used a Userform. Please give me some hint where to start.
Thanks
 
D

Dave Peterson

First, take a look at Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html

Just to get familiar with working with userforms.

The when you're done, try creating a userform (named userform1) that contains a
textbox (to get the input), a label (just for error messages) and two buttons
(cancel and ok).

Then in a general module, put this:

Option Explicit
Public RunWhen As Double
Public myVal As Variant
Public myDefaultValue As Long
Dim UF1 As UserForm1
Sub testme()
Set UF1 = New UserForm1
UF1.Show

Select Case LCase(myVal)
Case Is = "timedout"
myVal = myDefaultValue 'some default
Case Is = "cancel"
myVal = 0 'what to do if user cancelled
'exit sub '????
Case Else
myVal = CLng(myVal)
End Select

MsgBox myVal
End Sub
Sub KillForm()
myVal = "TimedOut"
Call KillTimer
Unload UF1
End Sub
Sub KillTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:="killForm", schedule:=False
On Error GoTo 0
End Sub

Then behind the userform, put this code:

Option Explicit
Dim blkProc As Boolean
Private Sub CommandButton1_Click()
myVal = "Cancel"
Call KillTimer
Unload Me
End Sub
Private Sub CommandButton2_Click()
Call KillTimer
If IsNumeric(Me.TextBox1.Value) Then
myVal = Me.TextBox1.Value
Unload Me
Else
Me.Label1.Caption = "Please enter a number"
End If
End Sub
Private Sub TextBox1_Change()
If blkProc = True Then
Exit Sub
Else
Call KillTimer
End If
End Sub
Private Sub UserForm_Initialize()
RunWhen = Now + TimeSerial(0, 1, 0) 'hr,min,sec
Application.OnTime RunWhen, "KillForm"
Me.Label1.Caption = ""

myDefaultValue = 4 'or whatever you want

blkProc = True
Me.TextBox1.Value = myDefaultValue
blkProc = False
End Sub

You'll get 3 different types of answers from the userform--Cancel (if the user
hit the cancel button), TimedOut (if the form times out) or a number.

I wasn't sure what to do if the user cancels.

You can read more about the scheduling procedures at Chip Pearson's site:
http://www.cpearson.com/excel/ontime.htm

=======
For testing purposes, you may want to use something smaller than a minute.

And this procedure kills the timer if the user clicks on a button or starts
typing in the textbox.
 
D

daniel chen

Hi Dave
I will do that, thanks.

Dave Peterson said:
First, take a look at Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html

Just to get familiar with working with userforms.

The when you're done, try creating a userform (named userform1) that
contains a
textbox (to get the input), a label (just for error messages) and two
buttons
(cancel and ok).

Then in a general module, put this:

Option Explicit
Public RunWhen As Double
Public myVal As Variant
Public myDefaultValue As Long
Dim UF1 As UserForm1
Sub testme()
Set UF1 = New UserForm1
UF1.Show

Select Case LCase(myVal)
Case Is = "timedout"
myVal = myDefaultValue 'some default
Case Is = "cancel"
myVal = 0 'what to do if user cancelled
'exit sub '????
Case Else
myVal = CLng(myVal)
End Select

MsgBox myVal
End Sub
Sub KillForm()
myVal = "TimedOut"
Call KillTimer
Unload UF1
End Sub
Sub KillTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:="killForm", schedule:=False
On Error GoTo 0
End Sub

Then behind the userform, put this code:

Option Explicit
Dim blkProc As Boolean
Private Sub CommandButton1_Click()
myVal = "Cancel"
Call KillTimer
Unload Me
End Sub
Private Sub CommandButton2_Click()
Call KillTimer
If IsNumeric(Me.TextBox1.Value) Then
myVal = Me.TextBox1.Value
Unload Me
Else
Me.Label1.Caption = "Please enter a number"
End If
End Sub
Private Sub TextBox1_Change()
If blkProc = True Then
Exit Sub
Else
Call KillTimer
End If
End Sub
Private Sub UserForm_Initialize()
RunWhen = Now + TimeSerial(0, 1, 0) 'hr,min,sec
Application.OnTime RunWhen, "KillForm"
Me.Label1.Caption = ""

myDefaultValue = 4 'or whatever you want

blkProc = True
Me.TextBox1.Value = myDefaultValue
blkProc = False
End Sub

You'll get 3 different types of answers from the userform--Cancel (if the
user
hit the cancel button), TimedOut (if the form times out) or a number.

I wasn't sure what to do if the user cancels.

You can read more about the scheduling procedures at Chip Pearson's site:
http://www.cpearson.com/excel/ontime.htm

=======
For testing purposes, you may want to use something smaller than a minute.

And this procedure kills the timer if the user clicks on a button or
starts
typing in the textbox.
Click to expand...
 
B

Bob Phillips

Some reading matter

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=168067
File Title: Microsoft(R) Visual Basic(R) for Applications Examples for
Controlling UserForms in Microsoft Excel 97

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp

Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top