Force only numbers entry in textbox userform

[IceSnakeR]

Hy,

I have this code which works fine when you manualy type the information allowing only numbers. If I use the paste function in the textbox("DN") userform I can enter everything, letters ... . The code bellow is set to KeyPress, is it possibile to verify the textbox when I click a button or on the exit from the textbox? I am new to VBA and try to learn as i go, the code underneath is from the web. I have another code from the web which has enabled copy/paste function in my file.

Private Sub DN_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If (KeyAscii > 47 And KeyAscii < 58) Then
KeyAscii = KeyAscii
Else
KeyAscii = 0
MsgBox ("ERROR")
End If


End Sub

Thanks,
Chris

 

[reberryjr]

Try this Chris

Private Sub txt_Weight_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Response As VbMsgBoxResult
If IsNumeric(Me.txt_Weight.Value) Then
    Me.txt_Weight = Format(Me.txt_Weight.Value, "#0.00")
Else
    Response = MsgBox("Please enter the Client's weight.")
    If Response = vbYes Then Me.txt_Weight.SetFocus
    Cancel = True
End If
End Sub

 

[IceSnakeR]

Thank you reberryjr. This is what I was looking for to do, just one last thing, my entry numbers sometimes start with a 0 and this code doesn`t leave the 0 in front of the inserted number.

 

[Rick Rothstein]

Paste all the code below, exactly as posted, into the UserForm's code module. This code will allow the user to type or paste only digits into the TextBox. See the commented section about controlling the maximum number of digits that can be placed in the TextBox (the code, as written, is set to a maximum of 6 digits). The below code assumes the TextBox is named TextBox1.

[table="width: 500"]
[tr]
	[td]Dim LastPosition As Long
Const PatternFilter As String = "*[!0-9]*"

[B][COLOR="#008000"]'  If you do not want to restrict the number of characters,
'  simply set the MaxLen to a huge number like (2^31-1)[/COLOR][/B]
Const MaxLen As Long = 6
 
Private Sub TextBox1_Change()
  Static LastText As String
  Static SecondTime As Boolean
  If Not SecondTime Then
    With TextBox1
     If .Text Like PatternFilter Or Len(.Text) > MaxLen Then
        Beep
        SecondTime = True
        .Text = LastText
        .SelStart = LastPosition
      Else
        LastText = .Text
      End If
    End With
  End If
  SecondTime = False
  [B][COLOR="#008000"]'Place any other TextBox1_Change event code here[/COLOR][/B]
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
    [B][COLOR="#008000"]'Place any other MouseDown event code here[/COLOR][/B]
  End With
End Sub
 
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBox1
    LastPosition = .SelStart
    [B][COLOR="#008000"]'Place any other KeyPress checking code here[/COLOR][/B]
  End With
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Paste all the code below, exactly as posted, into the UserForm's code module. This code will allow the user to type or paste only digits into the TextBox. See the commented section about controlling the maximum number of digits that can be placed in the TextBox (the code, as written, is set to a maximum of 6 digits). The below code assumes the TextBox is named TextBox1.

The code I posted in Message #4 was derived from code I developed back in the early 2000's for the compiled version of Visual Basic. That computer language did not have a way of disabling events so I developed the bit built around the SecondTime variable to handle the re-entry into the Change event caused by changes made inside of the Change event. It just occurred to me given that Excel's VBA language has a method to directly disable events that my Change procedure code can be simplified. Here is all of my Message #4 code modified to remove the SecondTime variable and replace it with event disabling...

[table="width: 500"]
[tr]
	[td]Dim LastPosition As Long
Const PatternFilter As String = "*[!0-9]*"

[B][COLOR="#008000"]'  If you do not want to restrict the number of characters,
'  simply set the MaxLen to a huge number like (2^31-1)[/COLOR][/B]
Const MaxLen As Long = 6
 
Private Sub TextBox1_Change()
  Static LastText As String
  Application.EnableEvents = False
  With TextBox1
   If .Text Like PatternFilter Or Len(.Text) > MaxLen Then
      Beep
      .Text = LastText
      .SelStart = LastPosition
    Else
      LastText = .Text
    End If
  End With
  Application.EnableEvents = True
  [B][COLOR="#008000"]'Place any other TextBox1_Change event code here[/COLOR][/B]
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
    [B][COLOR="#008000"]'Place any other MouseDown event code here[/COLOR][/B]
  End With
End Sub
 
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  With TextBox1
    LastPosition = .SelStart
    [B][COLOR="#008000"]'Place any other KeyPress checking code here[/COLOR][/B]
  End With
End Sub[/td]
[/tr]
[/table]

 

[IceSnakeR]

Thank you Rich Rothstein.