PDA

View Full Version : Application.InputBox Zero is False??



clvestin
10-18-2005, 06:53 PM
I have a short routine to generate a small report. I thought i had covered all the bases. But... well here's some code



msgliq = "Enter Current Liquifaction Rate in MM" & [vbCrLf] & "FT-162"
validentry = False
Do

liqrate = Application.InputBox(msgliq, "Rate", , , , , , 3)'Accepts numbers or text
'If liqrate = False Then Exit Do 'THIS IS THE LINE THAT FAILS
If liqrate = "" Then Exit Do

If IsNumeric(liqrate) Then
If liqrate >= 0 And liqrate <= 20 Then validentry = True
Else: msgliq = "Enter Current Liquifaction Rate in MM, eg 12.5"

End If
Loop Until validentry
'If liqrate = False Then finish 'To a sub to "clean up" and exit
If liqrate = "" Then liqrate = "0.00"
[g16] = liqrate



If one enters 0(zero) into this InputBox, this is seen as False and hence exits. I included this line in the event one used the Cancel option, in which case I want to exit the sub. Question: Why is zero being seen as False?? Can VBA be that binarily pedantic? In which case, how can I accept one entering a zero?

BlueCactus
10-18-2005, 10:18 PM
Zero and False are the same thing. You already have the answer in your code. If the user hits cancel, InputBox returns the empty string ("").

clvestin
10-19-2005, 07:34 AM
This regards Application.InputBox(the excel method) as opposed to vba's InputBox function. My question is..How does one enter a zero(numerical zero) into the box without it being interpreted as Boolean False??

Killian
10-19-2005, 08:25 AM
It's an interesting point...
I think the answer is to loop until your entry test is met or the return from the input box is Boolean False.
Then, you test your return value isn't "False" when cast as a string before you apply the value to the rangeDim msgliq As String
Dim liqrate As Variant
Dim validentry As Boolean

msgliq = "Enter Current Liquifaction Rate in MM" & [vbCrLf] & "FT-162"

Do
liqrate = Application.InputBox(msgliq, "Rate", , , , , , 3) 'Accepts numbers or text
If IsNumeric(liqrate) Then
If liqrate >= 0 And liqrate <= 20 Then
validentry = True
Else
msgliq = "Enter Current Liquifaction Rate in MM, eg 12.5"
End If
End If
Loop Until validentry Or liqrate = False

If CStr(liqrate) <> "False" Then
If liqrate = "" Then liqrate = "0.00"
[g16] = liqrate
End If
I was curious... why use a type 3 (numbers and text) when you only process numbers? If you changed that to type 1, you wouldn't need the IsNumeric test.

BlueCactus
10-19-2005, 10:51 AM
Your original code (including the comments) recognizes cancel ("") to exit the loop, and recognizes zero as a legitimate value for liqrate. So why do you need to test for false?

clvestin
10-21-2005, 08:43 AM
OK -this works for me-I need to test for 1)a valid entry(numbers between 0 and 20), 2)if one enters zero, its seen as numerical zero(Must I??), and 3) if one uses the cancel button. Hence the 3 in the App.InputBox parameter, testing for the empty string.


msgliq = "Enter Current Liquifaction Rate in MM" & [vbCrLf] & "FT-162"
Do
liqrate = Application.InputBox(msgliq, "Liquifaction_Rate", , , , , , 3)
If IsNumeric(liqrate) Then
If liqrate >= 0 And liqrate <= 20 Then validentry = True
Else: msgliq = "Enter Current Liquifaction Rate in MM, eg 12.5"
End If
Loop Until validentry Or liqrate = False Or liqrate = ""
If CStr(liqrate) <> "false" Then
If liqrate = "" Then liqrate = "0.00"
If CStr(liqrate) = "False" Then GoTo finish
[g16] = liqrate
End If

Killian
10-21-2005, 12:10 PM
you don't need to use a type 3 (text and numbers) or test for an empty string because Cancel returns boolean false, which you test for before you set the cell valueDim msgliq As String
Dim liqrate As Variant

msgliq = "Enter Current Liquifaction Rate in MM eg 12.5" & vbLf & "FT-162"

Do
liqrate = Application.InputBox(msgliq, "Rate", , , , , , 1)
Loop Until liqrate >= 0 And liqrate <= 20 Or liqrate = False

If CStr(liqrate) <> "False" Then
[g16] = liqrate
End If

Cyberdude
10-21-2005, 12:13 PM
Hi, clvestin! I think I just may have the solution...at least you can try it:
Sub CancelTest()
Dim Ans As String
Ans = Application.InputBox("Enter value", Type:=3)
On Error GoTo Finish
If Ans = 0 Then MsgBox "Ans=" & Ans
On Error GoTo 0
Finish:
End Sub
This is important: you MUST declare the reply variable "Ans" as a string. If the user presses CANCEL, then the On Error kicks in and sends him to the end of the macro. If he specifies 0, then it doesn't trigger the error, so the 0 is recognized