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

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)


"daniel chen" wrote in message
...
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

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

"Bob Phillips" wrote in message
...
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)


"daniel chen" wrote in message
...
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

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, _
procedu="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.


daniel chen wrote:

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

"Bob Phillips" wrote in message
...
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)


"daniel chen" wrote in message
...
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





--

Dave Peterson

Hi Dave
I will do that, thanks.

"Dave Peterson" wrote in message
...
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, _
procedu="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.


daniel chen wrote:

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

"Bob Phillips" wrote in message
...
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)


"daniel chen" wrote in message
...
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





--

Dave Peterson

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...FormsPartI.asp

Part II
http://msdn.microsoft.com/library/en...ormsPartII.asp


--

HTH

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


"daniel chen" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)


"daniel chen" wrote in message
...
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