Results 1 to 7 of 7

Format textbox in userform for currency

This is a discussion on Format textbox in userform for currency within the Excel Questions forums, part of the Question Forums category; I have a 2 textboxes in a userform. One (txtCPU1) will display the result of a lookup, and the other ...

  1. #1
    New Member
    Join Date
    Oct 2007
    Posts
    47

    Default Format textbox in userform for currency

    I have a 2 textboxes in a userform. One (txtCPU1) will display the result of a lookup, and the other (txtCost1) will display the result of a calculation.. How can I format the textboxs to have the values be in currency (ie $24.45) with the dollar sign and 2 decimals.
    Here is the code right now for the change:

    Code:
    Private Sub txtCPU1_Change()
        
        If txtQty1.Value = "" Then Exit Sub
        If txtCPU1.Value = "" Then Exit Sub
        txtCost1.Value = CDbl(txtQty1.Value) * CDbl(txtCPU1.Value)
        
    End Sub
    The txtQty1 box is already limited to to a number, and the lookup value is a currency amount from a worksheet. Thanks for your help.

  2. #2
    Board Regular
    Join Date
    Jan 2008
    Location
    No, Not There. Just West of There.
    Posts
    63

    Default Re: Format textbox in userform for currency

    Try this:

    Code:
     
    txtCost1.Value = format(CDbl(txtQty1.Value) * CDbl(txtCPU1.Value), "$###,##.00")

  3. #3
    New Member
    Join Date
    Oct 2007
    Posts
    47

    Default Re: Format textbox in userform for currency

    The last part of the code doesn't work.

    Code:
    txtCost1.Value = format(CDbl(txtQty1.Value) * CDbl(txtCPU1.Value), "$###,##.00")

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Location
    No, Not There. Just West of There.
    Posts
    63

    Default Re: Format textbox in userform for currency

    What are the data types for txtQty1 and txtCPU1?

    If one of these is not formatted as a number, your calculation will not work.

    Also, two other ideas:

    1.) When using the format function, you probably don't need CDbl.
    2.) Are you using the object in your actual code? userform1 example:

    HTML Code:
    If userform1.txtQty1.Value = "" Then Exit Sub
    If userform1.txtCPU1.Value = "" Then Exit Sub
    userform1.txtCost1.Value = format(userform1.txtQty1.Value * userform1.txtCPU1.Value), "$###,##.00")
    Last edited by Henson; May 8th, 2008 at 02:44 PM.

  5. #5
    New Member
    Join Date
    Oct 2007
    Posts
    47

    Default Re: Format textbox in userform for currency

    Sorry, I had entered the code wrong.

    But here is another problem. The box that is formated, is part of another sum formula. But it can't calculate because of the dollar sign. Is there a way to somehow exclude the dollar sign, or do I just have to live without it?

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Location
    No, Not There. Just West of There.
    Posts
    63

    Default Re: Format textbox in userform for currency

    Just create a variable for use in the next formula:

    Code:
     
    Dim x As Long
    x = txtQty1.Value * txtCPU1.Value
    Now you can also simplify your previous line of code:

    Code:
     
    txtCost1.Value = format(x, "$##,###.00")
    Last edited by Henson; May 8th, 2008 at 02:57 PM.

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,955

    Default Re: Format textbox in userform for currency

    Here's another approach.
    Code:
        txtCost1.Value = Format(txtQty1.Value * txtCPU1.Value, "Currency")
    If posting code please use code tags.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com