CODEWITHSUNDEEP
excelvba
AndroidDev
AndroidDev

Reputation: 21237

Restricting User Input to Integers on a Userform Textbox

I have a textbox on a userform that I am trying to restrict user input to only allow integer values. I am able to do this, but the behavior is a little weird. First, here is my code:

Private Sub txtAnswer_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
   If (KeyAscii >= 48) And (KeyAscii <= 57) Then
       Me.txtAnswer.SetFocus
   Else
       KeyAscii = 0
       Me.txtAnswer.SetFocus
   End If
 End Sub

The problem is that after the user has entered a value, the focus seems to go away from the textbox. Further, if the user does enter an integer value, this value is deleted from the textbox (i.e. the input gets "eaten"). The SetFocus lines are my attempt to make the control behave correctly, but they seem to have no effect.

All I want to do is make sure that the user doesn't enter something like "r" (or any other non-integer value) in the textbox. Any integer value >= 0 is perfectly acceptable (including multiple digit values like 10 or 1000000).

Can anybody see why my approach isn't working? I've tried a few different approaches and have searched around quite a bit, but I can't find something that works.

Thank you

Upvotes: 3

Views: 19126

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Taking it one step Ahead!

'~~> Disable Pasting CTRL V , SHIFT + INSERT
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If (Shift = 2 And KeyCode = vbKeyV) Or (Shift = 1 And KeyCode = vbKeyInsert) Then
        KeyCode = 0
    End If
End Sub

'~~> Preventing input of non numerics
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
      Case vbKey0 To vbKey9, vbKeyBack, vbKeyClear, vbKeyLeft, _
      vbKeyRight, vbKeyUp, vbKeyDown, vbKeyTab
      Case Else
        KeyAscii = 0
        Beep
    End Select
End Sub

Upvotes: 7

Reafidy
Reafidy

Reputation: 8431

You can also use regular expressions.

Private Sub txtAnswer_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    With CreateObject("VBScript.RegExp")
        .Pattern = "^\d*$"
        .IgnoreCase = True     

        If Not .Test(TextBox1.Value & Chr(KeyAscii)) Then KeyAscii = 0        
    End With

End Sub

The advantage being if you need to check for more complicated string combinations, say for negative integers like so: 

.Pattern = "^[-+]?\d*$"

or for another example no leading zero:

.Pattern = "^[1-9]{1}\d*$"

Upvotes: 5

Related Questions