VBA Numbers only in Textbox

[LeonardH]

I am trying to get a text box in a userform to only allow numerical input. Does anyone know a simple code for this? Many that I have tried have failed.

 

[MARK858]

Try putting the code below (from Ozgrid) in the Userforms module.

Private Sub TextBox1_Change()
    If TypeName(Me.ActiveControl) = "TextBox" Then
        With Me.ActiveControl
            If Not IsNumeric(.Value) And .Value <> vbNullString Then
                MsgBox "Sorry, only numbers allowed"
                .Value = vbNullString
            End If
        End With
    End If
End Sub

 

[Rick Rothstein]

Try putting the code below (from Ozgrid) in the Userforms module.

Private Sub TextBox1_Change()
    If TypeName(Me.ActiveControl) = "TextBox" Then
        With Me.ActiveControl
            If Not IsNumeric(.Value) And .Value <> vbNullString Then
                MsgBox "Sorry, only numbers allowed"
                .Value = vbNullString
            End If
        End With
    End If
End Sub

The problem with a Change event only procedure is that it will not prevent a user from pasting in bad data. In addition, I do not like the idea of popping that MessageBox up with every mistype. The following coordinated event code procedures (and one global event variable) will allow only proper numeric values in the TextBox whether typed or pasted in. Note the two colored lines of code... the OP did not describe what he meant by "numeric input... digits only or floating point numbers, so I provided code for either. Uncomment the red line of code for digits only OR uncomment the blue line of code for floating point numbers (only uncommen one of the lines of code and leave the other commented out).

Dim LastPosition As Long
 
Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox1
     ' Digits Only
     'If .Text Like "*[!0-9]*" Then
     ' Floating Point Numbers
     If .Text Like "*[!0-9.]*" Or .Text Like "*.*.*" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub
 
Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
                               ByVal X As Single, ByVal Y As Single)
  With TextBox1
    LastPosition = .SelStart
    'Place any other MouseDown event code here
  End With
End Sub
 
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBox1
    LastPosition = .SelStart
    'Place any other KeyPress checking code here
  End With
End Sub

 

[ZVI]

This code allows typing of digits & dot symbol and block others:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 
  Select Case KeyAscii
 
    Case 48 To 59
      ' Allow digits - do nothing
 
    Case 46
      ' Allow only one dot symbol
      If InStr(ActiveControl, ".") Then KeyAscii = 0
 
    Case Else
      ' Block others
      KeyAscii = 0
 
  End Select
 
End Sub

Note: Pasting from another control is not controlled

 

[Rick Rothstein]

This code allows typing of digits & dot symbol and block others:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 
  Select Case KeyAscii
 
    Case 48 To 59
      ' Allow digits - do nothing
 
    Case 46
      ' Allow only one dot symbol
      If InStr(ActiveControl, ".") Then KeyAscii = 0
 
    Case Else
      ' Block others
      KeyAscii = 0
 
  End Select
 
End Sub

As I pointed out to MARK858 for the code he posted, your code also allows the user to paste in bad data. See the code I posted in Message #3 for a method that gets around that problem.

 

[ZVI]

Thanks Rick! I've added the note to my code.
To solve the pasting issue you can use this additional code:

Private Sub TextBox1_Enter()
  With New DataObject
    .GetFromClipboard
    .SetText Trim(Str(Val(.GetText)))
    .PutInClipboard
  End With
End Sub

Trim(Str(... )) gives independence from local decimal separator like comma

 

[Rick Rothstein]

To solve the pasting issue you can use this additional code:

Private Sub TextBox1_Enter()
   With New DataObject
     .GetFromClipboard
     .SetText Trim(Str(Val(.GetText)))
     .PutInClipboard
   End With
End Sub

I see two problems with that solution. First, on my XL2010 workbook.... true, using that code stops CTRL+V from being able to paste bad data that was previously copied into the clipboard buffer, but two blank spaces get placed into the TextBox instead. Second, and more importantly, because the code is located in the Enter event, it will not stop a user from bringing up the UserForm, then switching to a different window and copying bad data into the Clipboard buffer, then returning to the TextBox and pasting it in at that point. True, that is not something one would expect a user to do, but more than likely, any subsequent code would either crash or output bad results if the user did. I'm still pushing for my solution in Message #3... it should be completely foolproof (it was developed and debugged many, many years ago for use in the compiled version of VB and later adapted to the minor differences between the old VB's Forms and Excel's VBA UserForms).

 

[ZVI]

Nice code Rick, thanks for sharing!
BTW, supporting of negative numbers would be useful too
Regards,
Vlad

 

[Rick Rothstein]

Nice code Rick, thanks for sharing!

You are quite welcome... I am glad you liked it.

BTW, supporting of negative numbers would be useful too

No problem... that is one of the nice things about the structure of my code, it is easy to modify for different conditions. Here is the code with two new color sections added to allow the digits only (purple) and the floating point numbers (green) to have a leading plus or minus sign (as before, uncomment the one you want and make sure the others are commented out)...

Dim LastPosition As Long
 
Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox1
     ' Digits Only (no plus or minus)
     'If .Text Like "*[!0-9]*" Then
     ' Digits Only (plus or minus allowed)
     'If .Text Like "*[!0-9+-]*" Or .Text Like "?*[+-]*" Then
     ' Floating Point Numbers (no plus or minus)
     'If .Text Like "*[!0-9.]*" Or .Text Like "*.*.*" Then
     ' Floating Point Numbers (plus or minus allowed)
     If .Text Like "*[!0-9.+-]*" Or .Text Like "?*[+-]*" Or .Text Like "*.*.*" Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
End Sub
 
Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
                               ByVal X As Single, ByVal Y As Single)
  With TextBox1
    LastPosition = .SelStart
    'Place any other MouseDown event code here
  End With
End Sub
 
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBox1
    LastPosition = .SelStart
    'Place any other KeyPress checking code here
  End With
End Sub

 

[MARK858]

Rick, that is a neat and comprehensive bit of code. Filed away for future reference.

Have a great Christmas