VBA Inputbox issue with values higher than 1

[Diaxus]

After multiple searches using different keywords, I've yet to find anyone having even a similar problem, so I'm not sure what to think here. I have an inputbox that requests a value (building it now so no code written for invalid value types yet). I've written code that adds the value input to the value in a cell, then inserts the total into that cell. The code works only if the value in the inputbox is 1.

This is my code (on a smartphone so formatting will look strange I think):

Sub FuelBuy_Click ()
Dim Answer As (I've tried Single, Double, and String)
Dim PurchaseDatabase As Range
Dim rngMyCell As Range
Dim Buy As Single

Set PurchaseDatabase = Sheets ("Purchase Database").Range ("B8:B1000")
Answer = InputBox ("How many will you purchase?", "Fuel Purchase")

If Answer = vbOk Then

MsgBox ("made it here")
Buy = Answer + Sheets ("Purchase Database").Range("M6")
MsgBox (Buy)
Sheets ("Purchase Database").Range ("M6") = Buy
End If
End Sub


The two message boxes are being used to determine how far the macro is going before terminating. Again, if I enter 1 into the input box, it goes all the way and inserts the new total into M6, but any other numeric value doesn't even ping the first message box. Any help is greatly appreciated. Thank you for your time.

 

[mikerickson]

An InputBox can only return a string.

But the real problem is the testing of Answer against vbOK.

Try

Dim Answer as String

Do
    Answer = InputBox ("How many will you purchase?", "Fuel Purchase")

    If StrPtr(Answer) = 0 Then
        Rem cancel pressed
        Exit Sub
    If IsNumeric(Answer) Then
        If 0 < Val(Answer) Then
            MsgBox ("made it here")
            Buy = Val(Answer) + Sheets ("Purchase Database").Range("M6")
            MsgBox (Buy)
            Sheets ("Purchase Database").Range ("M6") = Buy
        Else
            MsgBox "Postive numers only"
        End If
    Else
        MsgBox "please enter only numbers"
    End If

Loop until Val(Answer) > 0

 

[KolGuyXcel]

Hi Mike,

It seems the macro is missing an EndIf statement after the Exit Sub line! Otherwise the macro is basically doing nothing!

Dim Answer as String

Do
    Answer = InputBox ("How many will you purchase?", "Fuel Purchase")

    If StrPtr(Answer) = 0 Then
        Rem cancel pressed
        Exit Sub
    [COLOR=#ff0000]End If[/COLOR]
    If IsNumeric(Answer) Then
        If 0 < Val(Answer) Then
            MsgBox ("made it here")
            Buy = Val(Answer) + Sheets ("Purchase Database").Range("M6")
            MsgBox (Buy)
            Sheets ("Purchase Database").Range ("M6") = Buy
        Else
            MsgBox "Postive numers only"
        End If
    Else
        MsgBox "please enter only numbers"
    End If

Loop until Val(Answer) > 0

 

[Diaxus]

Thank you Mike, and KolGuy for catching the endif. As you can probably tell I'm used to msgbox and thought inputbox worked the same. One last question though; does the "0 < val (answer) line deal with the cancel error or do I need to put in an On Error line as well?

 

[Rick Rothstein]

It seems the macro is missing an EndIf statement after the Exit Sub line! Otherwise the macro is basically doing nothing!

Dim Answer as String

Do
    Answer = InputBox ("How many will you purchase?", "Fuel Purchase")

    If StrPtr(Answer) = 0 Then
        Rem cancel pressed
        Exit Sub
    [B][COLOR="#FF0000"]Else[/COLOR][/B]If IsNumeric(Answer) Then
        If 0 < Val(Answer) Then
            MsgBox ("made it here")
            Buy = Val(Answer) + Sheets ("Purchase Database").Range("M6")
            MsgBox (Buy)
            Sheets ("Purchase Database").Range ("M6") = Buy
        Else
            MsgBox "Postive num[B][COLOR="#FF0000"]b[/COLOR][/B]ers only"
        End If
    Else
        MsgBox "please enter only numbers"
    End If

Loop until Val(Answer) > 0

Instead of adding the "End If" statement, I think I would have just made the second "If" statement an "ElseIf" instead.

 

[KolGuyXcel]

Thank you Mike, and KolGuy for catching the endif. As you can probably tell I'm used to msgbox and thought inputbox worked the same. One last question though; does the "0 < val (answer) line deal with the cancel error or do I need to put in an On Error line as well?

If you are referring to hitting the Cancel Button of the Input Box or closing the Input Box by clicking the Cross button on the top right corner of the Box as the "Cancel Error", then

If StrPtr(Answer) = 0 Then
Exit Sub

segment of the code is taking care of that!

 

[Diaxus]

Just plugged this in and it works like a charm, except I did have to enter

Dim Buy as Single

to finish it. Thanks everyone!

Btw, I've seen some of your tutorials Rick, they are really helpful. Thank you!