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 on how to accomplish this.

I would appreciate any help, thanks for your time

Josh

[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

[Toppers]

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

"jhahes" wrote:

>
> 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 on
> how to accomplish this.
>
> I would appreciate any help, thanks for your time
>
> Josh
>
>
> --
> jhahes
> ------------------------------------------------------------------------
> jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
> View this thread: http://www.excelforum.com/showthread...hreadid=378273
>
>

[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 :

> 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
>
> "jhahes" wrote:
>
> >
> > 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 on how to accomplish this.
> >
> > I would appreciate any help, thanks for your time
> >
> > Josh
> >
> >
> > --
> > jhahes
> > --------------------------------------------------------------------
> > ---- jhahes's Profile:
> > http://www.excelforum.com/member.php...o&userid=23596
> > View this thread:
> > http://www.excelforum.com/showthread...hreadid=378273
> >
> >

[Toppers]

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" wrote:

>
> 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 :
>
> > 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
> >
> > "jhahes" wrote:
> >
> > >
> > > 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 on how to accomplish this.
> > >
> > > I would appreciate any help, thanks for your time
> > >
> > > Josh
> > >
> > >
> > > --
> > > jhahes
> > > --------------------------------------------------------------------
> > > ---- jhahes's Profile:
> > > http://www.excelforum.com/member.php...o&userid=23596
> > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=378273
> > >
> > >
>

[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 :

> And DANGEROUS code .. no explosion here!

[Toppers]

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

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

"keepITcool" wrote:

>
> '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 :
>
> > And DANGEROUS code .. no explosion here!
>