How do I limit TextBox data entry to 2 digits?

  • Thread starter Thread starter Jock
  • Start date Start date
Use code like the following:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Len(Me.TextBox1.Text) >= 2 Then
KeyAscii = 0 ' cancel key press
End If
End Sub


Change both occurrences of TextBox1 to the name of your text box.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
I missed the "digits" part of your question. My previous reply
restricts TextBox1 to 2 characters, not necessarily 2 digits. Use the
following instead. It limits input to 2 digits.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Len(Me.TextBox1.Text) >= 2 Then
KeyAscii = 0
Else
Select Case KeyAscii
Case vbKey0 To vbKey9
' OK
Case Else
KeyAscii = 0
End Select
End If
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Unfortunately, that method will not prevent users from pasting in "invalid"
characters. Here is some code that I first posted to the compiled VB
newsgroups, but have modified for Excel's VBA world... it will restrict the
characters allowed in the TextBox (whether typed or pasted) to only the
digits 0 thru 6 (the allowable characters are controlled by the list in the
2nd If..Then statement); also note that I have the routine Beep for invalid
characters, although the display of a text message is doable if desired...

'************* START OF CODE *************
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With TextBox1
If .Text Like "*[!0-6]*" 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
'************* END OF CODE *************
 
Sorry, I forgot to read what I had written in the old message I used as the
basis for my posting. Here is the code the way it *should* have been posted
(to accept all 10 digits, not just the 0 to 6)...

'************* START OF CODE *************
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With TextBox1
If .Text Like "*[!0-9]*" Or Len(.Text) > 2 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
'************* END OF CODE *************

--
Rick (MVP - Excel)


Rick Rothstein said:
Unfortunately, that method will not prevent users from pasting in
"invalid" characters. Here is some code that I first posted to the
compiled VB newsgroups, but have modified for Excel's VBA world... it will
restrict the characters allowed in the TextBox (whether typed or pasted)
to only the digits 0 thru 6 (the allowable characters are controlled by
the list in the 2nd If..Then statement); also note that I have the routine
Beep for invalid characters, although the display of a text message is
doable if desired...

'************* START OF CODE *************
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
With TextBox1
If .Text Like "*[!0-6]*" 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
'************* END OF CODE *************

--
Rick (MVP - Excel)


Chip Pearson said:
I missed the "digits" part of your question. My previous reply
restricts TextBox1 to 2 characters, not necessarily 2 digits. Use the
following instead. It limits input to 2 digits.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Len(Me.TextBox1.Text) >= 2 Then
KeyAscii = 0
Else
Select Case KeyAscii
Case vbKey0 To vbKey9
' OK
Case Else
KeyAscii = 0
End Select
End If
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
Click to expand...
Click to expand...
 
Back
Top