Adding numbers using Excel Forms

Question

I have created an Excel form which has five text boxes. These TextBoxes accept any numerical value. Once the five numbers have been entered there is a sixth Textbox that shows the sum of these values. The sum of these values should always be 100%. Here is my code:

Private Sub TextBox1_Change()

Dim Value As Single

Value = Val(TextBox1.Value) + Val(TextBox2.Value) + Val(TextBox3.Value) + Val(TextBox4.Value) + Val(TextBox5.Value)

TextBox6.Value = Value

End Sub

I have the same code for all Text-box change events. Unfortunately this is not working. When I enter 10, 20, 30, 40 as my input, instead of displaying 100% it shows 10203040 as the result.

What am I doing wrong?

What is wrong is your expectation that the sum of five values should be 100%. When you sum five values you're going to get the sum of the values. If you want a percentage, than you have to compare the sum to some other value. You want the sum as a percentage of what exactly? — Brian Pressler
– Brian Pressler, Commented Oct 16, 2015 at 19:45
Sorry I should have been clearer. I am summing up 5 values and I am checking if it's 100 or not. If its not, I am displaying an error. The problem I have is just with the summing part. I don't think the Val function I am using is converting the text to numbers. — Amritha
– Amritha, Commented Oct 16, 2015 at 19:47
Ah gotcha... I was a bit confused. I thought you were expecting TextBox6 to show 100%. — Brian Pressler
– Brian Pressler, Commented Oct 16, 2015 at 19:52
Since Value is a single, TextBox1.Value to TextBox5.Value would automatically be converted to numeric. The Val is not strictly necessary. — Tony Dallimore
– Tony Dallimore, Commented Oct 16, 2015 at 19:55

Brian Pressler · Accepted Answer · 2015-10-16 20:21:06Z

1

I believe the function you need to use is CDbl or CSng which converts to a double or single floating point numbers. It won't handle blanks or non-numeric data entered so you might need to check for invalid data entered first. Something like this should work:

Value = 0
If IsNumeric(TextBox1.Value) Then Value = Value + CSng(TextBox1.Value)
If IsNumeric(TextBox2.Value) Then Value = Value + CSng(TextBox2.Value)
If IsNumeric(TextBox3.Value) Then Value = Value + CSng(TextBox3.Value)
If IsNumeric(TextBox4.Value) Then Value = Value + CSng(TextBox4.Value)
If IsNumeric(TextBox5.Value) Then Value = Value + CSng(TextBox5.Value)

TextBox6.Value = Value

edited Oct 16, 2015 at 20:21

answered Oct 16, 2015 at 19:51

Brian Pressler

6,7332 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Amritha Over a year ago

When I execute the CDbl code, it says type mismatch and does not execute the code.

Brian Pressler Over a year ago

You might have to check for invalid data entered before including it in the sum. I updated my answer to show how you can do that.

Tim Williams Over a year ago

IIf doesn't short-circuit: it will always evaluate both the True and False parts. In the Immediate window ? iif(true, "OK", 1/0) gives a "division by zero" error...

Collectives™ on Stack Overflow

Adding numbers using Excel Forms

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related