locked
VBA EXCEL RRS feed

  • Question

  • i want help in vba  code .
    MY USER FORM TEXTBOX MAXLENGTH IS 14 .
    1ST LETTER MUST "B, C, E, R, X"
    2ND, 3RD ,4TH LETTER MUST BE "0 TO 9"
    5 AND 6TH LETTER MUST BE "A TO Z"
    7 TO 14 CHARACTER MUS CONTAIN LETTER FROM "0 TO 9".

    EXAMPLE

    B111AB12345678

    Tuesday, December 22, 2020 3:30 PM

All replies

  • In the userform's code module:

    Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        Dim re As Object
        Set re = CreateObject("VBScript.RegExp")
        re.Pattern = "[BCERX]\d{3}[A-Z]{2}\d{8}"
        If Not re.Test(Me.TextBox1) Then
            MsgBox "Text does not satisfy all conditions!", vbCritical
            Cancel = True
        End If
    End Sub

    where TextBox1 is the name of the text box.

    P.S. Please turn off Caps Lock.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)


    Tuesday, December 22, 2020 4:02 PM
  • The code seems to accept incorrect sequences like “xB111AB12345678x”, but it can be easily adjusted, I think.

    Wednesday, December 23, 2020 8:13 PM
  • That can be rectified by changing the pattern:

        re.Pattern = "^[BCERX]\d{3}[A-Z]{2}\d{8}$"


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Wednesday, December 23, 2020 9:43 PM
  • Thanks all of you for your help. 

    once again thanks

    Thursday, December 24, 2020 1:53 AM
  • re.Pattern = "^[BCERX]{1}\d{3}[A-Z]{2}\d{8}$"

    IT WORKS FOR ME .

    Thursday, December 24, 2020 2:10 AM