prompt if numeric values inputed is less than five digits

[stoey]

hi again,

I have a userform which has a textbox for employee id. If an employee enters less than 5 digits, it should prompt the user via msg box that he needs to enter 5 digits for employee id. However my coding doesnt work...can someone help me correct it? thanks

Private Sub TextBox1_Enter()
If TextBox1.MaxLength > 5 Then
    MsgBox "Employee ID must be 5 digits", vbInformation
 End If
End Sub

Stoey

[Leith Ross]

Hello Stoey,

You need to place the code into the TextBox_Exit event. This allows you to set Cancel. This will put the focus back on your Text Box and not set the focus on the next control in the tabbing sequence. This will highlight the entry.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If TextBox1.MaxLength > 5 Then
    MsgBox "Employee ID must be 5 digits", vbInformation
    Cancel = True
    TextBox1.SelStart = 0
    TextBox1.SelLength = TextBox1.Text
  End If
End Sub

[stoey]

hi leith,

just tried the macro you corrected but i am receiving an error message "Run-time error '1004':
Application-define or object-define error" when running the program. Also, the msg box prompt doesnt appear...any idea?

thanks

stoey

[Leith Ross]

Hello Stoey,

I missed the MaxLength in the code the first time. Should be Length(TextBox1.Value) . Here is the corrected code...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If Len(TextBox1.Value) > 5 Then
    MsgBox "Employee ID must be 5 digits", vbInformation
    Cancel = True
    TextBox1.SelStart = 0
    TextBox1.SelLength = TextBox1.Text
  End If
End Sub

[stoey]

hi again leith,


i'm not sure why its not working on my end...but i attached a sample file where in you can try it...currently i am still receiving the same error message. i appreciate your patience and kindness...

thanks,
stoey

[croix]

I think you want a less than sign - "<" not ">"

If Len(TextBox1.Value) < 5 Then

I made this change in your spreadsheet and when I entered a 4 digit id the msgbox popped up and focus returned to the id box.