User Form Formulas - Sum

[Nole68]

I have a user form where the user puts in a number of cases (QuantityTextBox(1-6) ). I then used a fixed weight to calculate the Net and Gross weight. I then want the total weight so I didn't know how to sum it so I added each box. The problem I have is that if the QuantityTextBox is left blank the sum doesn't fill in. There are times where certain items won't be entered. If they type a zero in the quantity field the sum formula works.

Please Login or Register  to view this content.

Thanks!
Nole68

[gmr4evr1]

Please upload a sample workbook with your userform and code, it will make it a lot easier for people to help you out.
Also, when posting code, please use code tags. To do this, highlight your code in the post then click the # symbol. You will have to click Edit post at the bottom right hand side of your post first.

[Nole68]

Thanks gmr4evr1.. I won't be able to post the workbook as it has too much sales information in it. I can write this in excel =sum(A1:A6) but not in VBA. It should be simple, but I am sure it's not sum and the zero needing to be in there is something too.

[gmr4evr1]

Going to be difficult to help but, have your tried adding .Value to your code?
Example

Please Login or Register  to view this content.

[Nole68]

I went ahead and stripped out as much as I can... I don't want to have to type zeros in the qty text boxes and still get the total of them all to add up in the total gross and net weight texst box.

[Nole68]

thanks gmr4evr1 ... Didn't change anything

[gmr4evr1]

Ok, I realize that this may not be the best solution, but, it works. Problem is, it's going to require adding a bit of code to your existing code.
Put this at the beginning of your CommandButton4_Click() code...After On Error Resume Next and before 'Calculate export value

Please Login or Register  to view this content.

Do this for each of your quantity, net weight and gross weight textboxes and it should work.

[Nole68]

Thanks.. I am heading out to catch a plane on vacation so I will look into this Monday. What about a better formula for summing up the NetWeightCaseTextBox1 - 5 and Gross??? That formula seems kind of green but I can't find something for sum.

[gmr4evr1]

When it comes to using a formula in code, I am totally lost, so I'm afraid I wont be able to help you there.
Have a safe trip and enjoy the vacay.

[gmr4evr1]

I got kinda bored so I went ahead and wrote the code for you that I was talking about.

Please Login or Register  to view this content.

[mikerickson]

The entrys in a textbox are strings, not numbers, they can't be added.
You can use the Val function to convert from string to number

Please Login or Register  to view this content.

[Tinbendr]

You can also set the default value of the textbox to 0 in the properties panel.

[gmr4evr1]

Good point David, I hadn't thought of that. Maybe that would work better for the OP.

[gmr4evr1]

Mike's solution works great as well.

[Nole68]

I liked the default value idea but it didn't seem to work so I went with Mike's solution. PERFECT!! Because the variable was not set it defaulted to strings, so using the Val Function the variable was changed to number and allowed the calculation?

What about the Sum of the 6 text boxes. Is there a better sum formula?

Thanks for all your help guys!

Tom

[mikerickson]

You might try this, to see if Excels automatic type conversion will kick in.

Please Login or Register  to view this content.

But I dislike trusting automatic conversion (who knows what defaults they might change next version).

Please Login or Register  to view this content.

Tells the program exactly what you want it to do. You don't have to trust the default settings.