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:

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?

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.


"excelent" wrote:

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:

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?

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:

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.


"excelent" wrote:

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:

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?

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.


"excelent" wrote:

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:

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.


"excelent" wrote:

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:

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?