+ Reply to Thread
Results 1 to 7 of 7

add text box values

  1. #1
    jhahes
    Guest

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

    I would appreciate any help, thanks for your time

    Josh

  2. #2
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    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

  3. #3
    Toppers
    Guest

    RE: 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. #4
    keepITcool
    Guest

    Re: 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. #5
    Toppers
    Guest

    Re: 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. #6
    keepITcool
    Guest

    Re: 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. #7
    Toppers
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1