Format numbers in TextBox as you type

Question

Is there is any way to format numbers in TextBox (on a UserForm) as you type?

This way it makes easy to see what figure is being entered.

My desired format is: #,##0.00

Answer 1

Try this code, you just type the number naturally and will displayed in formatted number:

Private Sub TextBox1_Change()
    Dim str As String
    Dim pos As Integer
    str = TextBox1.Text
    pos = TextBox1.SelStart
    If str <> "" Then
        On Error Resume Next
        If InStr(str, ".") > 0 Then
            str = Format(CDbl(str), "#,##0.00")
        Else
            str = Format(CDbl(str), "#,##0")
        End If
        If Err.Number = 0 Then
            TextBox1.Text = str
            If InStr(str, ".") > 0 Then
                TextBox1.SelStart = pos + (Len(str) - Len(TextBox1.Text))
            Else
                TextBox1.SelStart = pos + 1
            End If
        End If
    End If
End Sub

Answer 2

Try that:

Private Sub Text1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode > 47 And KeyCode < 58 Then
        Text1.Tag = Text1.Tag & Chr$(KeyCode)
    ElseIf KeyCode = vbKeyBack And Len(Text1.Tag) > 0 Then
        Text1.Tag = Left$(Text1.Tag, Len(Text1.Tag) - 1)
    End If

    Text1 = FormatCurrency$(Val(Text1.Tag) / 100, 2, vbTrue, vbFalse, vbTrue)
    Text1.SelStart = 65535
    KeyCode = 0

End Sub

Answer 3

This could be considered a slightly "Above the Average" question in terms of difficulty for a newbie so I am going to answer this :)

VBA doesn't have what you call a Masked Text Box where you can set formats as #,##0.00. You can only do a masked textbox for accepting passwords but that is altogether a different thing.

Here is something I quickly came up with. Hope this is what you want?

Dim CursorPosition As Long
Dim boolSkip As Boolean
Dim countCheck As Long

Private Sub TextBox1_Change()
    '~~> This avoids refiring of the event
    If boolSkip = True Then
        boolSkip = False
        Exit Sub
    End If

    '~~> Get current cursor postion
    CursorPosition = TextBox1.SelStart
    boolSkip = True

    '~~> Format the text
    TextBox1.Text = Format(TextBox1.Text, "#,##0.00")

    '~~> Re-position the cursor
    If InStr(1, TextBox1.Text, ".") - 1 > 0 Then _
    TextBox1.SelStart = InStr(1, TextBox1.Text, ".") - 1
End Sub

You can take it to a slightly higher level by including this code as well. This ensures that the user only types numbers.

'~~> Numeric Textbox with Decimal Check
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case vbKey0 To vbKey9, vbKeyBack, vbKeyClear, vbKeyDelete, _
        vbKeyLeft, vbKeyRight, vbKeyUp, vbKeyDown, vbKeyTab
            If KeyAscii = 46 Then If InStr(1, TextBox1.Text, ".") Then KeyAscii = 0
        Case Else
            KeyAscii = 0
            Beep
    End Select
End Sub

In Action