Summing up values in label captions

[Spoony]

Hi

I have spent many hours trying this with no luck.

I have a number of labels (8) which get filled in correctly (by dbl clicking in a Listbox) and are formatted in the following way

Format(NewOrderForm.Controls("PurPrice1").Caption, "#,###.##") - (there is also PurPrice2,3,4...8)

This is also multiplied by the value in a text box.

This all works fine, however, I'm trying to keep a running total in another label whenever the "Qty" textbox is changed.

I can get it to work without the formatting but I really need to have the formatting as 1,234.56. The VAL function I found out, will not accept the comma.

This is what I have so far. I'm sure there are cleverer ways of doing this but I'm just a novice (and self taught).


Private Sub Qty1_Change()


Dim LngTotalValue, P1, P2 As Double


If NewOrderForm.Qty1.Text = "" Then


NewOrderForm.Controls("TotalPrice1").Caption = ""
Else
NewOrderForm.Controls("TotalPrice1").Caption = Format(NewOrderForm.Controls("PurPrice1").Caption, "#,###.##") * Val(NewOrderForm.Controls("Qty1").Text)




End If


NewOrderForm.TotalPrice1.Caption = Format(NewOrderForm.TotalPrice1.Caption, "#,###.##")


NewOrderForm.Lb_TotalValue.Caption = Val(NewOrderForm.Controls("TotalPrice1").Caption) + _
Val(NewOrderForm.Controls("TotalPrice2").Caption) + _
Val(NewOrderForm.Controls("TotalPrice3").Caption) + _
Val(NewOrderForm.Controls("TotalPrice4").Caption) + _
Val(NewOrderForm.Controls("TotalPrice5").Caption) + _
Val(NewOrderForm.Controls("TotalPrice6").Caption) + _
Val(NewOrderForm.Controls("TotalPrice7").Caption) + _
Val(NewOrderForm.Controls("TotalPrice8").Caption)




NewOrderForm.Lb_TotalValue.Caption = Format(NewOrderForm.Lb_TotalValue.Caption, "#,###.##")

End Sub


Any help would be appreciated.

Thanks

Steve

[mancubus]

try changing Val to CDbl

did it work?

[Spoony]

No, but I have just managed to get it to work by replacing the comma with "" and then re-formatting.

Appreciate your response though

[SamT]

I'm just a novice (and self taught).Captions and TextBoxs contain only Strings. Math requires numbers.

Convert numerical Strings into Numbers with CLng, CDbl, CCur, and a few others. Generally, CDble will work in most cases where an Integer or "long integer" is not required.

Personally, I would add an additional step, that recorded, multiplied, and summed all Numerical values before formatting and applying those values to any String valued Controls, Probably with an array.

Something similar to

Dim SalesDetails
Const Q as Long = 2 'Makes code easier to comprehend.
Const P As Long = 1

Private Sub UserForm_Initialize()
Redim Sales Details(1 to 8, 1 to 2)
End Sub

Private Sub Qty1_Change()
Dim LngTotalValue As Variant 'Result of your one line declaration
Dim P1 as Variant
DimP2 As Double

With Me 'Me is the UserForm, in this case
If .Qty1.Text = "" Then
.Controls("TotalPrice1").Caption = ""
Else
SalesDetails(1, P) = CDble((.PurPrice1) 'Not sure of this syntax, and not going to investigate
SalesDetails(1, Q) = CDble(.Qty1)
.TotalPrice1.Caption = Format(SalesDetails(1, P) * SalesDetails(1, Q), "#,###.##")
Etc
etc
etc

Private Sub Qty2_Change()
'Use 2 vice 1 in this sub
SalesDetails(2, P) = CDble((.PurPrice2)
SalesDetails(2, Q) = CDble(.Qty2)
.TotalPrice2.Caption = Format(SalesDetails(2, P) * SalesDetails(2, Q), "#,###.##")
Etc
etc
etc


For 1 = 1 to 8
TotalValue = TotalValue + SalesDetails(i, P) * SalesDetails(i, Q)
Next

[mancubus]

The Val function recognizes only the period (.) as a valid decimal separator. When different decimal separators are used, as in international applications, use CDbl instead to convert a string to a number.


https://msdn.microsoft.com/en-us/library/gg264296(v=office.14).aspx

[Spoony]

Many thanks for you input and help

very useful

Steve

[SamT]

You're welcome. I'm very glad I could help you.