inputbox

[RobcPettit]

Hi Im using
Dim appliances as integer
appsagain:

appliances = InputBox(Prompt:="Appliances.", _
Title:="Sheet" & a, Default:="0")
If appliances < 0 Then
MsgBox ("Number to small")
GoTo appsagain
ElseIf appliances > 40 Then
MsgBox ("Number > 40")
GoTo appsagain
End If
What I want to do is if text is used in the input box then restart. At
the momment if text is enetered then the code crashes. Any ideas.
Regards Robert

 

[Nigel]

Change appliances to a string, the input box returns the string, then test
by taking the Val(appliances)........ see below


Dim appliances As String
appsagain:

appliances = InputBox(Prompt:="Appliances.", _
Title:="Sheet" & a, Default:="0")
If Val(appliances) < 0 Then
MsgBox ("Number to small")
GoTo appsagain
ElseIf Val(appliances) > 40 Then
MsgBox ("Number > 40")
GoTo appsagain
End If

 

[Bob Phillips]

You can trap it within the inputbox itself. And you need to allow an escape
door.

As you are testing for <0, not <=0, I assume 0 is a valid value, so

Dim appliances As Variant
Dim AllDone As Boolean

Do
appliances = Application.InputBox( _
Prompt:="Appliances.", _
Title:="Sheet" & a, Default:="0", _
Type:=1)
If appliances = False Then
'cancel our
AllDone = True
ElseIf appliances < 0 Then
MsgBox "Number to small"
ElseIf appliances > 40 Then
MsgBox "Number > 40"
Else
AllDone = True
End If
Loop Until AllDone

 

[Rick Rothstein \(MVP - VB\)]

You will do yourself the biggest favor programming-wise if you learn **not**
to rely on the GoTo statement for your program execution. Give this a try...

Do
Appliances = InputBox(Prompt:="Appliances.", _
Title:="Sheet" & A, Default:=0)
If Appliances Like String(Len(Appliances), "#") Then
If Appliances = "" Then
Appliances = 0
Exit Do
ElseIf Appliances < 0 Then
MsgBox "Negative numbers are not allowed!"
ElseIf Appliances > 40 Then
MsgBox "Only numbers between 0 and 40 are allowed!"
Else
Exit Do
End If
Else
MsgBox "Your input was not an integer!"
End If
Loop

Note: If the user clicks Cancel, Appliances will be assigned the value 0.

Rick