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.
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 Beep End Select End Sub