Calculated Textbox not showing proper results.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On a userform I have three textboxes. Two that people use to enter amounts
and a third that automatically displays the sum of the two amounts.

amount1.text
amount2.text
total.text

In the Total Textbox I use this (code borrowed from another thread):

Total.Text = Val(Amount1.Text) + Val(Amount2.Text)

This works if the amounts are under 1,000.00 but when the amounts are over
1,000 the code reads to the decimal. For example:

Example 1 Under 1,000
----------------
Amount1 = 500.00
Amount2 = 500.00
Total = 1,000.00

Example 2 Over 1,000
-----------------
Amount1 = 500.00
Amount2 = 10,000.00 <= sees 10,000 as just 10
Total = 510.00 <= should be 10,500.00

I think the problem is caused by using the Val function. I have tried using
CDbl but then I get a "Type mismatch" error if one of the Amount Textboxes
are blank. I guess it is expecting a number and gets a blank space instead.
What am I doing wrong? Any suggestions would be much appreciated.

Overall this problem is only a small part of an awesome spreadsheet, however
after working on this specific issue for three days I thought it better to
throw in the towel and ask for help.

So HELP!! Anyone?
 
Total.Value = Format(CDec(Amount1.Text) + CDec(Amount2.Text), "0,000.00")

vorks fine in my DK-version - maby u have to swap "0,000.00" to "0.000,00"


"zootieb" skrev:
 
Thanks for the reply ...

.... however when I use CDec I get the following results.

Sample 1: if field is zero
--------------------------
Amount1 = 500.00
Amount2 = 0.00
Total = 0,500.00

Sample 2: if field is blank
---------------------------
Amount1 = 500.00
Amount2 = ""
I get "Type mismatch" error.

If I use the "0.000.00" formatting I get the same results (leading zero and
"type mismatch" errors) as above except the numbers look like this 500.990.00
when the number should look more like this 500.99.
 
Private Sub Total_Enter()
If IsNumeric(Amount1.Text) Then
Amount1.Value = Format(Amount1.Text, "#,###.00")
Else
Amount1.Value = 0
End If
If IsNumeric(Amount2.Text) Then
Amount2.Value = Format(Amount2.Text, "#,###.00")
Else
Amount2.Value = 0
End If

Total.Value = Format(CDec(Amount1.Value) + CDec(Amount2.Value), "#,###.00")
End Sub

And again u may have to swap "#,###.00" to "#.###,00"
not sure bout that

"zootieb" skrev:
 
Awesome dude!! It works!!!

That was it, I don't know my formatting. Thanks for the assist, my
spreadsheet is a masterpiece with your help.
 
Back
Top