validate textbox value in Userform

[robertvdb]

I have a Userform with some textboxes. In one of the textboxes, only values 0 to 9 can be entered. Nothing else.

If the user makes an error here, then I can trap this error when he presses the "OK" button.

However, I want to trap it as soon as he leaves the textbox.

So he needs to receive an error message, and his wrong input must be undone.

Can anyone help ?

 

[dmt32]

Hi,
you just need to limit the data type allowed in the control & the max character lenght.

Try following & see if does what you want

Place in a STANDARD module

Function NumbersOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    Select Case KeyAscii
'valid entries [0-9]
    Case 48 To 57
'valid
    Case Else
'cancel
        KeyAscii = 0
    End Select
   
    Set NumbersOnly = KeyAscii
   
    If KeyAscii = 0 Then MsgBox "You can Enter a single number 0-9 only", 16, "Invalid Entry"
End Function

and to call from your textbox

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    With Me.ActiveControl
        .AutoTab = True
        .MaxLength = 1
    End With
    KeyAscii = NumbersOnly(KeyAscii)
End Sub

Change textbox name as required.

Function can be used by other textboxes in your project as needed.


Hope helpful

Dave

 

[robertvdb]

Thanks Dave, works fine !

 

[dmt32]

Thanks Dave, works fine !

welcome glad suggestion helps & appreciate your feedback

Dave