My Blog

Format numbers in Excel TextBox as you type

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 helps someone?

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

Screen Shot

And now we are ready to code.


    Option Explicit
    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
    '~~> 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
        End Select
    End Sub