Loop an InputBox Until User Selects vbOk

[JarekM]

Hi, I have a code for the inbox below. The first inbox chooses how many numbers I can sum together, and I want to skip this part, so it just goes to the second inbox. This way I will be able to choose how many digits I want to sum, so I can keep on adding digits until I press "ok", then the textbox, "txtLFWall" will be populated with the result of the sum. By the way, when I press the "ok" there is an error that shows up. Is there anyone that has an idea on how to fix this or how to do it?

Here is the code:

Private Sub CommandButton11_Click()
Dim TotalNum    As Variant
'Dim TotalNum1     As String
Dim Hits        As Long
Dim SumNums     As Variant
Dim EnteredNum  As Double

'Name = vbNullString
TotalNum = InputBox("How Many Numbers Do You Want to Sum?")
'TotalNum 1 = InputBox("How Many Numbers Do You Want to Sum?")
If TotalNum = "" Then
    Exit Sub
End If

    For i = 1 To TotalNum
        EnteredNum = InputBox("Enter Number Here")   
        SumNums = SumNums + EnteredNum
        Hits = Hits + 1
    Next i
'  If EnteredNum = vbOK Then
 Me.txtLFWall = SumNums
End Sub

Best Regards

 

[dmt32]

Hi,
With the InputBox Function, pressing OK button returns whatever is in the text box it does not return vbOK which is a member of vbMsgBoxResult enumeration a constant that has a value of 1.

To do what you want in a loop you would need to be able to provide an exit from it but, from what I think I understand, you want to have two options?

Loop until TotalNum reached

Or

Loop until you decide to exit

Only way I can think this could be done is to prompt the user to press cancel button to exit if the latter option in use.

Just an idea but maybe something like following will help

Private Sub CommandButton11_Click()
    Dim Hits As Long, SumNums As Long
    Dim EnteredNum  As Variant, TotalNum   As Variant
    
    TotalNum = InputBox("How Many Numbers Do You Want to Sum?")
'cancel pressed
    If StrPtr(TotalNum) = 0 Then Exit Sub
    
    Do
        Prompt = "Enter Number Here" & Chr(10) & "SumNums " & SumNums & Chr(10) & Chr(10) & _
        IIf(Val(TotalNum) > 0, Hits + 1 & " of " & TotalNum, "Press Cancel To Exit")
        EnteredNum = InputBox(Prompt)
'cancel pressed
        If StrPtr(EnteredNum) = 0 Then Exit Do
'check numeric entry
        If IsNumeric(EnteredNum) Then
            Hits = Hits + 1
            SumNums = SumNums + Val(EnteredNum)
            If Val(TotalNum) > 0 And Val(TotalNum) = Hits Then Exit Do
        End If
    Loop
    
    Me.txtLFWall = SumNums
End Sub

maybe another here will have an alternative suggestion

Dave

 

[jasonb75]

Or a simpler version

Dim SumNums     As Double
Dim EnteredNum  As Variant

Do
    EnteredNum = InputBox("Enter Number Here")
        If EnteredNum = "" Then Exit Do
        SumNums = SumNums + EnteredNum
Loop
    Me.txtLFWall = SumNums

Pressing enter on an empty box will exit the loop.

 

[JarekM]

Thank you Jason a lot, the code that you have provided worked nicely and was exactly want I wanted, and I can't believe the solution was that simple, but dmt32 gave me an idea that I would like to have implemented into Jason's code. Sometimes I might add a lot of sums together and I would forget or I would like to recheck if the values that I added were the correct ones or if I am not adding the same values over again. So, I would like it if someone or you guys could help me find a way to show the values that I am adding up in the inbox.

Thanks Again

 

[mikerickson]

Try

Dim strTerms As String, uiInput As String
Dim dblSum As Double
Dim strPrompt As String

Do
    
    strPrompt = strPrompt & vbCr & "add another?"
    uiInput = InputBox(strPrompt)
    If strTerms = vbNullString Then
        strTerms = CStr(Val(uiInput))
    Else
        strTerms = CStr(Val(uiInput)) & "+" & strTerms
    End If
    dblSum = dblSum + Val(uiInput)
    strPrompt = strTerms & "=" & CStr(dblSum)
Loop Until StrPtr(uiInput) = 0

 

[JarekM]

Thank you Mike, your code was exactly what I was thinking about and it worked nicely, but I still would like to have two small things changed.

First thing is that whenever I press the ok button it should then populate the sum on a userform control instead of cancel like under your code. When I press the cancel button it should clear the string in the input box, so I will be able to start from the beginning again.

Secondly, Is there a possibility that there is a code that will let me change the values in the input box individually after I already added some of them up.

 

[Akuini]

Secondly, Is there a possibility that there is a code that will let me change the values in the input box individually after I already added some of them up.

Why not just type the number in the textbox (without using Inputbox)?
So you type in the textbox something like: 2+4+35
Then write a code (in the commandbutton) to sum the numbers, something like:

TextBox1.Value = Evaluate(TextBox1.Value)

That way you can edit the numbers as needed before you hit the button.

 

[JarekM]

I was so focused on the input box that I didn't realize that there was a simpler way to do it. I'm not a VBA genius, I just know a little about it.
You helped me out a lot.

Thank you Akuini

 

[Akuini]

I was so focused on the input box that I didn't realize that there was a simpler way to do it.

There's still something to do to make it work correctly.
We need to 'trap' the above code, in case you type something that cause an error or something that can't be evaluated as number. So amend the code like this:

Private Sub CommandButton1_Click()
    If IsNumeric(Evaluate(TextBox1.Value)) Then
    TextBox1.Value = Evaluate(TextBox1.Value)
    End If
End Sub

And if you need to know the sums each time you type a number, you can place a Label next to the texboxt, and in Sub TextBox1_Change add a code like this:

Private Sub TextBox1_Change()
    If IsNumeric(Evaluate(TextBox1.Value)) Then
    Label1.Caption = Evaluate(TextBox1.Value) 'show the sums in Label1 each time you type a number
    End If
End Sub

 

[JarekM]

Thanks again Akuini, you gave really good suggestions.

The reason why I use the inputbox is because...

1. I am entering sometimes very large values in a small textbox, as you can see in the picture which is the first link (the red box).
2. So I wanted a separate textbox where I can see the values that I am entering in (this textbox will be larger and it is presented in the second image which is the second link)(the textbox is also the green one). In this text box I would have the ability to change the values as well. (I know how to hide the textbox and have it show up. I just don't know how to transfer the values from the red box to the green box.)
3. This way I would enter the values in the red textbox and after I press enter key, the red textbox will clear itself and then the value would then go into the green textbox.
4. And then your "Evaluation" code would apply to the green textbox.

http://imgur.com/a/CC3xwDN

https://imgur.com/a/7r2vz72