application.inputbox 0-value VS CANCEL

[Legacy 146544]

Hi all,

I'm using the application.inputbox function to get a value from the user (as currency). If the user clicks cancel, the procedure should end. But it should be possible for the user to input the numerical value 0 and then click OK.

How can I know if a 0 was entered or if cancel was clicked?

 

[pgc01]

Hi

Use a Variant. If the user presses Cancel the InputBox() returns False.

Try:

Sub Test()
Dim vRet As Variant
 
vRet = Application.InputBox("Enter Value", Type:=1)
MsgBox vRet
End Sub

 

[Legacy 146544]

Thx pgc, but didn't get it to work yet.

I tried the code below and in both cases
-pressing CANCEL
-typing 0 and pressing OK
the CASE FALSE is executed

Any way to differentiate between them?

Sub Test()
Dim vRet As Variant

vRet = Application.InputBox("Enter Value", Type:=1)
MsgBox vRet
Select Case vRet
Case False
MsgBox "FALSE"
Case 0
MsgBox "0"
End Select

End Sub

 

[pgc01]

You can test if the return value is boolean, for ex.:

Sub Test()
Dim vRet As Variant
 
vRet = Application.InputBox("Enter Value", Type:=1)
 
If (VarType(vRet) = vbBoolean) And (vRet = False) Then
        MsgBox "False"
ElseIf vRet = 0 Then
        MsgBox "Zero"
Else
        MsgBox vRet
End If
End Sub

Remark: you just need an If (boolean false) and an Else (numeric value), I divided it in 3 branches just to test the return value False vs. 0.

 

[Legacy 146544]

That's it. Thank you very much.

....and learned something. Was until now always 'not getting exactly what I needed' with the IsNumeric and IsDate functions when evaluating input.

From now on I'll use the VarType() function, thx man.