add text box values

[jhahes]

If I have 3 text boxes on a userform

1 is Price

1 is Qty

1 is Total



How do I get the Total text box to display the qty*price result

Right now I do this, but it isn't working

x = format(TxtPrice,"Currency")
y = format(TxtQty,"#,###)
z = format(TxtTotal,"Currency")

But my error displays as type mismatch, I am probably not even close o
how to accomplish this.

I would appreciate any help, thanks for your time

Jos

 

[mangesh_yadav]

Private Sub TxtPrice_Change()
TxtTotal = TxtPrice * TxtQty
End Sub

Private Sub TxtQty_Change()
TxtTotal = TxtPrice * TxtQty
End Sub

You need to have the above 2 event macros which trigger the change
event when you enter the price or the quantity. The Total will be
automatically calculated.

Mangesh

 

[Guest]

Hi,
Insert this code in your Userform:


Private Sub txtPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtPrice.value = Format(txtPrice.value, "£#,##0.00") ' Adjust to your
currency
End Sub

Private Sub txtQty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtQty.value = Format(txtQty.value, "0") ' Format as number
TxtTotal.Value = Format(txtQty.value * txtPrice.value, "£#,##0.00") 'adjust
to your currency
End Sub


HTH

 

[keepITcool]

toppers..

VERY dangerous code...
as updating the quantity will update the amount,
but changing the price will leave the amount unaffected.

Also you've not solved problems:
a text with currency code will raise an error when you try to use it in
computation... or when a comma is used as decimal separator.

following code is not perfect either but functional.


Option Explicit
Const NUMFMT = "€0.00" 'Note that format localizes the decsep!

Private Sub txtQty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtQty = Format(TxtToVal(txtQty), 0)
Call UpdateAmount
End Sub
Private Sub txtPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtPrice = Format(TxtToVal(txtPrice), NUMFMT)
Call UpdateAmount
End Sub

Private Sub UpdateAmount()
txtAmount = Format(TxtToVal(txtPrice) * TxtToVal(txtQty), NUMFMT)
End Sub

Private Function TxtToVal(ByVal sTxt$) As Double
Dim i%, sChr$, sVal$
For i = 1 To Len(sTxt)
sChr = Mid(sTxt, i, 1)
Select Case sChr
Case 0 To 9, "."
sVal = sVal & sChr
Case Application.DecimalSeparator
sVal = sVal & "."
End Select
Next
TxtToVal = Val(sVal)
End Function



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Toppers wrote :

 

[Guest]

Hi,
Correct on first but wrong on second (at least in Excel 2003). Price
entered with commas computes OK. e.g. £123.456,789.75 gives no problem as
format for TxtPrice includes ","


And DANGEROUS code .. no explosion here!


The following address your first point and adds check for numeric data:

Private Sub txtPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not isnumeric(txtPrice.value) then
Msgbox "Price must be numeric"
Exit Sub
end if
' Calculate if Qty has been entered (first time)
If IsNumeric(txtQty) Then txttotal.Value = Format(txtQty.Value *
txtPrice.Value, "£#,##0.00")
txtPrice.Value = Format(txtPrice.Value, "£#,##0.00")
End Sub

Private Sub txtQty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not isnumeric(txtQty.value) then
Msgbox "Quantity must be numeric"
Exit Sub
end if
txtQty.Value = Format(txtQty.Value, "0") ' Format as number
txttotal.Value = Format(txtQty.Value * txtPrice.Value, "£#,##0.00")
End Sub

 

[keepITcool]

'the boss' may explode if invoices were sent with wrong amounts <g>

and as i said my code was functional (with NUMFMT set to x0.00)
but not perfect (as demonstrated when combinations of . and , are
entered.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Toppers wrote :

 

[Guest]

Agreed! If I were the boss !!**&&???

I realised from Mangesh's post that I hadn't allowed for both field changing
- mea culpa!