ExcelBanter - add text box values

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - add text box values (https://www.excelbanter.com/excel-programming/331505-add-text-box-values.html)

add text box values

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

--
jhahe
-----------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...fo&userid=2359
View this thread: http://www.excelforum.com/showthread.php?threadid=37827

add text box values

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

--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378273

add text box values

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

add text box values

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

add text box values

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

add text box values

'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!

add text box values

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!