excel - Apply Regex into a textbox form VBA

Question

I need your help guys. How can I limit my textboxes? This is my Form Below.

What I need is to create if statement for the following textboxes: Pallet ID: Must only contain 9 numbers Carton ID: Must only contain 10 numbers Serials (all 6): Must startwith FOC and only contain CAPS and Numbers [A-Z][0-9]

I did this in Javascript but now I need to have a backup in a excelsheet. Any ideas?

Thanks for your attention,

Answer 1

I'v created a sample User_Form and a CommandButton (like a "confirm" button) that's once it's pressed it checks that all the values entered in the TextBoxes are according to the rules.

The code below will get you started, it checks the values in "Pallet ID" (9-digits) and "Carton ID" (10-digits).

Code

Option Explicit

Private Sub CommandButton1_Click()

Dim Reg1 As Object
Dim Reg2 As Object

Dim RegMatches As Object

' ====== Test PalletID_Tb =====
Set Reg1 = CreateObject("VBScript.RegExp")
With Reg1
    .Global = True
    .IgnoreCase = True
    .Pattern = "d{9,9}" ' Match any set of 9 digits
End With

Set RegMatches = Reg1.Execute(Me.PalletID_Tb.Value)

If RegMatches.Count = 1 Then '<-- make sure there is only 1 match (9 digits and not 18 or 27)
    MsgBox "Value in Pallet ID is OK"
Else
    MsgBox "Pallet ID must have a 9 digit format"
    Me.PalletID_Tb.Value = ""
    Exit Sub
End If

' ====== Test CartonID_Tb =====
Set Reg2 = CreateObject("VBScript.RegExp")
With Reg2
    .Global = True
    .IgnoreCase = True
    .Pattern = "d{10,10}" ' Match any set of 10 digits
End With

Set RegMatches = Reg2.Execute(Me.CartonID_Tb.Value)

If RegMatches.Count = 1 Then '<-- make sure there is only 1 match (10 digits and not 20)
    MsgBox "Value in Carton ID is OK"
Else
    MsgBox "Carton ID must have a 10 digit format"
    Me.CartonID_Tb.Value = ""
    Exit Sub
End If

' Do something if passed the 2 conditions

End Sub