Inputbox won't exit sub when cancel is clicked

[Pookiemeister]

Sub InptBx1()
    answ = InputBox("How many times does this lot split?", "Split Lot")
    If vbCancel = 2 Then Exit Sub
    If answ = "" Or answ < 2 Or Not IsNumeric(answ) Then
        Do
            MsgBox "Answer must be a number and the number must be greater than 1.", vbCritical, "Invalid Entry"
            Call InptBx1
        Loop Until answ <> "" And answ > 1
        frmPackageYield.Show vbModeless
    End If

End Sub

When the user clicks the "Ok" button and even though the Cancel button was NOT clicked, it acts like it was and exits the sub. Also, if I comment out the first IF statement and run the InputBox and click the Cancel button, it doesn't close, it continues on to the

If answ = "" Or answ < 2 Or Not IsNumeric(answ) Then

statement.
Why?? Before I continue adding more code to this(mainly error trapping). I need to first fix this issue. I really hope my explanation makes sense.
Thank You.

 

[Pookiemeister]

Sorry about the misleading title. Didn't know how to change it after I posted my question.

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Sub InptBx1()
    Dim answ As String
    
    Do
    answ = InputBox("How many times does this lot split?", "Split Lot")
'cancel pressed
    If StrPtr(answ) = 0 Then Exit Sub
    If Val(answ) > 1 Then Exit Do
        MsgBox "Answer must be a number and the number must be greater than 1.", vbCritical, "Invalid Entry"
    Loop

    frmPackageYield.Show vbModeless
End Sub

Dave

 

[Pookiemeister]

Thanks Dave. It worked. Can you explain what StrPtr and Val does.
Thank you again

 

[dmt32]

Hi,

Gad update worked ok for you

StrPtr only works with InputBox Function (simple inputbox)

The strptr function must be passed a string as its parameter where it returns the location in memory where that string is held. If the string does hold anything the function will return a zero value.

if the empty string is “” as a result of the user clicking OK button with an empty InputBox, the string exists and the function will return a value other than zero which is why the Inputbox does not exit the loop.

Val Function returns the numbers contained in a string as a numeric value of appropriate type. The function will stop reading (without error) anything that it does not recognise as a number so if user enters ABC it will return zero (0) If users enters 123ABC it will return 123



You can read more in VBA helpfile



Dave

 

[Pookiemeister]

Thank you so very much. That's the best explanation I've read so far. After you posted your solution, I searched what Val and StrPtr does. Your answer was short yet very detailed. Thanks again.

 

[dmt32]

Thank you so very much. That's the best explanation I've read so far. After you posted your solution, I searched what Val and StrPtr does. Your answer was short yet very detailed. Thanks again.

most welcome - in my advancing years, I sometimes get a little muddled but please solution & explanations have helped

Dave