Excel Userform with a field to display subtotal of amounts entered in data fields

I have never known how to create a "Userform" in Excel other than just taking one of the worksheets and creating a form on it using various cells and columns.  Since I have found user forms in the VB coding want to understand a couple things.  Viewed some videos online but none show me how to get a subtotal to display before you have "saved" the new inputs.

Essentially I have an input form that the user will key in 10 - 20 amounts.  Tried adding a text box to display a subtotal and then in the "Value" field and the "ControlSource" property fields putting a formula like "=txtbox1 + txtbox2"  but as you know that doesn't work.  How do you get a subtotal to display somewhere on the User Form??????


Also, in the text box fields where users will key in amounts, is there a way to format the field to display as "currency"?
wlwebbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieData ProcessorCommented:
Don't use ControlSource for this, use code to sum the textboxes.

For example to sum 20 textboxes named Textbox1-20,  put the total in another textbox called txtSubTotal with a command button named cmdSubTotal you could use something like this:
Private Sub cmdSubTotal_Click()
Dim I As Long
Dim dblSubTotal As Double
      
        For I = 1 To 20
             dblSubTotal = dblSubTotal+ Val(UserForm1.Controls("TextBox" & I).Value)
        Next I
         
        txtSubTotal.Value = dblSubTotal
End Sub

Open in new window

This will only work if you don't format as Currency, also I know this is run via a command button but it could be adapted to work based on another event.
0
wlwebbAuthor Commented:
Copied and pasted the above..  Get Run-time error '424'
Object required..

0
NorieData ProcessorCommented:
What controls do you have on the userform and what are they named?

Also, what's the name of the userform?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

p912sCommented:
Hello,

Attached is a sample workbook with a userform and controls that mimic what you're doing.

HTH

Scot

27515564.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieData ProcessorCommented:
Scott

That code won't work unless the system currency is the $.

Which it could very well be.:)
0
NorieData ProcessorCommented:
Oops, forgot to add - if you use Currency for the format instead of using a specific symbol it will work as the values will be formatted with the correct currency symbol.
0
p912sCommented:
That's what was asked for is there a way to format the field to display as "currency"? and they're in the US Eastern Standard Time (GMT-05:00), happens to be where I live...

Thanks

Scot
0
NorieData ProcessorCommented:
Apologies I assumed the main question was about summing the textboxes.

Using Currency isn't a bad idea though.
0
p912sCommented:
No apologies needed. Sorry if I sounded short, I'm generally a little blunt, or so I've been told... :)
0
NorieData ProcessorCommented:
No problem.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.