VBA Text Box displaying Currency

I have 开发者_如何学Pythona form with a number of text boxes for user input (this is in a User Form not on the spreadsheet). I have a few boxes that are related to currency and I need them to show the comma and decimal point as the user enters their criteria into the box. So far I have found a bunch of the same formulas online but when I input my number into the box it goes with 4.00 (if i hit 4 first) and all i can change after that is the second 0. Here is something similar I see online:

textbox1 = format(textbox1, "$#,##0.00")

Also seen some with cDbl

No matter what I try it won't let me enter anything more than the first number I enter. I need help. Thanks!

Formatting as the user types in data gets very tricky. May be better to format after the entry is complete.
Entry can also be validated and old value restored if entry deemed invalid

Dim TextBox1oldValue As String

Private Sub TextBox1_AfterUpdate()
    If IsNumeric(TextBox1) Then
        TextBox1 = Format(TextBox1, "$#,##0.00")
    Else
        TextBox1 = TextBox1oldValue
    End If
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If IsNumeric(TextBox1) Then
        TextBox1oldValue = Format(TextBox1, "$#,##0.00")
    End If
End Sub

Private Sub UserForm_Initialize()
    TextBox1oldValue = "$0.00"
    TextBox1 = "$0.00"
End Sub

You need to use the TextBox Change event, like:

Private Sub TextBox1_Change()    
  If TextBox1 = vbNullString Then Exit Sub    
  If IsNumeric(TextBox1) Then CurrencyTransform(TextBox1)
End Sub

You then create the CurrencyTransform function to modify what it shows in the TextBox.

Try simply this...

Private sub textbox1_AfterUpdate()
    textbox1 = format(textbox1, "$#,##0.00")
end sub

I wrote this inspired by chris' solution. It works while user is typing!

Private waiting As Boolean

Private Sub TextBox1_Change()
    If waiting Then Exit Sub
    
    waiting = True
    TextBox1 = formatAsCurrency(TextBox1)
    waiting = False
End Sub

Private Function formatAsCurrency(v As String)
    If v = "" Then
        formatAsCurrency = Format(0, "0.00")
    Else
        Dim vv As Variant
        vv = Replace(v, ",", "")
        vv = Replace(vv, ".", "")
        formatAsCurrency = Format(vv / 100, "#,##0.00")
    End If
End Function

Try this:

Private Sub TextBox1_Change()
    TextBox1.Value = Format(TextBox1.Value, "$#,##0.00")
End Sub

This worked for me just fine, so it should help you as well.

If you want to do calculations that involve multiple text boxes, don't use .value after the name of the text box. Instead, use val( before the name of the text box while following it with an end parenthesis. I used .value and got weird results. Instead of, for example, $100 for TextBox1.Value + TextBox2.Value where TextBox1.Valueis equal to $25 and TextBox2.Value is equal to $75, I would get "$25$75".