Summing up numbers from a single textbox on a userform

Lori_H

New Member
Joined
Apr 15, 2016
Messages
2
Hello all,

I've been trying to figure this one out on my own, but can't seem to. So I turn to you all!

Here's what I'd like to do:

On a userform, I'd have two textboxes: One for a person's name, and one for the amounts of refunds they may have processed. i.e., Joe has turned in handwritten worksheet to me that says he processed three refunds for 2.00, 4.99 and 3.50.

Sally will type "Joe" into the name textbox. She'll then type "2 4.99 3.50" or "2.00, 4.99, 3.50" into the second textbox.

I'd like to take the numbers she enters into the second textbox and add them together with the result being placed in a cell on a worksheet.

What I haven't been able to figure out is how to separate out the list of numbers (either space or comma delimited) and sum them up.

Suggestions??

Much appreciation in advance!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Maybe something like this...

Code:
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
    [color=darkblue]Dim[/color] sText [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] vRefunds [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] vAmount [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] MySum [color=darkblue]As[/color] [color=darkblue]Double[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    sText = Me.TextBox2.Value
    sText = Replace(sText, ",", " ")
    sText = Application.Trim(sText)
    vRefunds = Split(sText, " ")
    MySum = 0
    [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vRefunds)
        vAmount = vRefunds(i)
        [color=darkblue]If[/color] IsNumeric(vAmount) [color=darkblue]Then[/color]
            MySum = MySum + [color=darkblue]CDbl[/color](vAmount)
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    MsgBox MySum
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Note that the code needs to be placed in the code module for the UserForm. Change the name of the CommandButton and TextBox accordingly.

Hope this helps!
 
Upvote 0
You could try this but note it has absolutely no error checking or validation.
Code:
varSum = Evaluate(Replace(TextBox1.Value, " ", "+")

Range("A1").Value = varSum
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,699
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top