Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: VBA Numbers only in Textbox

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Numbers only in Textbox

    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.

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Numbers only in Textbox

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

    Code:
    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
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,249
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Numbers only in Textbox

    Quote Originally Posted by MARK858 View Post
    Try putting the code below (from Ozgrid) in the Userforms module.

    Code:
    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).

    Code:
    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
    Last edited by Rick Rothstein; Dec 25th, 2013 at 12:28 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,646
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Numbers only in Textbox

    This code allows typing of digits & dot symbol and block others:
    Code:
    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
    Last edited by ZVI; Dec 25th, 2013 at 12:37 AM.
    Vladimir Zakharov

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,249
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Numbers only in Textbox

    Quote Originally Posted by ZVI View Post
    This code allows typing of digits & dot symbol and block others:
    Code:
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,646
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Numbers only in Textbox

    Thanks Rick! I've added the note to my code.
    To solve the pasting issue you can use this additional code:
    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
    Last edited by ZVI; Dec 25th, 2013 at 12:57 AM.
    Vladimir Zakharov

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,249
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Numbers only in Textbox

    Quote Originally Posted by ZVI View Post
    To solve the pasting issue you can use this additional code:
    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).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,646
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Numbers only in Textbox

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

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,249
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA Numbers only in Textbox

    Quote Originally Posted by ZVI View Post
    Nice code Rick, thanks for sharing!
    You are quite welcome... I am glad you liked it.


    Quote Originally Posted by ZVI View Post
    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)...

    Code:
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Numbers only in Textbox

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

    Have a great Christmas
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •