Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif using Values, but returning text or values | Excel Discussion (Misc queries) | |||
Plotting XY with X text values? Credit ratings and their ROE values | Charts and Charting in Excel | |||
Top 5 values with corresponding text | Excel Discussion (Misc queries) | |||
Text values to numeric values | Excel Discussion (Misc queries) | |||
Text Values | Excel Discussion (Misc queries) |