UserForm Textbox Specific Format

[AlexRoberts]

Please can somebody help me.

I have created a UserForm in Excel which is complete apart from one small problem.

I have a text box into which I want the user to enter only 16 characters and I have set the max length property for this. But I also would like the data to be entered as follows, 3 Alpnumeric characters, 12 numeric characters, a “-“ and then 4 numeric characters e.g. AB-20101216-0001

If the data input does not match this criteria then a message box should appear.

Thank you.

[StephenR]

This perhaps?

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not Me.TextBox1.Value Like "???############-####" Then
    MsgBox "No"
    Cancel = True
End If

End Sub

[AlexRoberts]

Thank you Stephen.

I modified the code to "AB-########-#####" and it works a dream.

Because I want to make things neat and tidy is there a way to postion the cursor back to the beginning of the string when the input does not match the criteria.

[jwright650]

Because I want to make things neat and tidy is there a way to postion the cursor back to the beginning of the string when the input does not match the criteria.

Yes you can, Set the focus to the textbox.
Try Adding this (see below) and see if that works for you.

Me.TextBox1.SetFocus

[broro183]

hi,

In addition to Stephen's & John's suggestions, try playing around with the "textbox1.sel..." properties, for example:

Option Explicit

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.TextBox1
        If Not .Value Like "???############-####" Then
            MsgBox "No"
            .SetFocus
            .SelStart = 3
            .SelLength = Len(Me.TextBox1) - 3
            Cancel = True
        End If
    End With
End Sub

hth
Rob

[snb]

I prefer not to bother users with messageboxes as if they did something 'wrong'. It also slows the ease with which a user uses your application.

Based on the example in your first post:

Private Sub TextBox1_Change()
'    Application.EnableEvents = False

  With TextBox1
    Select Case Len(.Text)
    Case 1, 2
      .Text = UCase(.Text)
      If Asc(Right(.Text, 1)) < 65 Or Asc(Right(.Text, 1)) > 91 Then .Text = Left(.Text, Len(.Text) - 1)
      If Len(.Text) = 2 Then .Text = .Text & "-"
    Case 4 To 11, 13 To 16
      If Val(Right(.Text, 1)) = 0 Then .Text = Left(.Text, Len(.Text) - 1)
      If Len(.Text) = 11 Then .Text = .Text & "-"
    End Select
  End With

'    Application.EnableEvents = False
End Sub