PDA

View Full Version : Sum textboxes on userform



Glaswegian
02-28-2008, 03:56 AM
Hi again

I have some textboxes on a form and I'm trying to sum them and put the total in another textbox on the same form. Not all of the textboxes will have a value. Values will be in the format

20,000

where the comma is used as the separator. There are no decimal places required, i.e whole figures only.

I created a separate routine for this and then try to call the routine from the Change Event of each textbox.

Sub AddUpAll()
txbTotcost.Value = CDbl(txbODcost.Value) + CDbl(txbTLcost.Value) + CDbl(txbAFcost.Value) + CDbl(txbTLcost.Value) + _
CDbl(txbBCcost.Value) + CDbl(txbPaycost.Value) + CDbl(txbCredcost.Value)
txbTotcost.Value = Format(txbTotcost.Value, "#,##0")
End Sub

Private Sub txbTLcost_Change()
txbTLcost.Value = Format(txbTLcost.Value, "#,##0")
AddUpAll
End Sub

However, the code crashes with a Type Mismatch error on the first line of the AddUpAll sub. I thought this was related to using CDbl and tried Val instead but Val only recognises a decimal point as a valid separator.

Can anyone give me a clue where I'm going wrong here?

Thanks.

Bob Phillips
02-28-2008, 04:38 AM
Sub AddUpAll()
txbTotcost.Value = Format(Val(Replace(txbODcost.Value, ",", "")) + _
Val(Replace(txbTLcost.Value, ",", "")) + _
Val(Replace(txbAFcost.Value, ",", "")) + _
Val(Replace(txbTLcost.Value, ",", "")) + _
Val(Replace(txbBCcost.Value, ",", "")) + _
Val(Replace(txbPaycost.Value, ",", "")) + _
Val(Replace(txbCredcost.Value, ",", "")), "#,##0")
End Sub

Glaswegian
02-28-2008, 04:58 AM
Thanks xld - that works perfectly. :thumb I didn't consider the Replace part, probably because I'm pretty hopeless at formulae.

I now just need to sort out my code as I must have included a stray value somewhere - either that or maths has changed considerably since I went to school. :rotlaugh: