Check formatting of TextBox Entry

[kperk96]

Hi. I have a section of vba for my UserForm that I need help with. I have a TextBox where the user can enter a single product number or a series of product numbers (unlimited). The product numbers must be 7 digits and separated by a comma and space. I have figured out a way to check and correct the comma space issue but there is probably a better way. Definitely need help with checking the 7 digits. If any number in the array is not 7 digits then I want to prompt the user with a message to correct, as seen in my current VBA below.

Example of user inputs:
1). 1234567
2). 1234567, 1234567, 1234567, ..... (just a comma, a comma and space, or any combination may be used to separate product numbers)

Current VBA:

>>> use code - tags <<<

Dim A() As String, B() As String, Rslt As Boolean
Dim myVar As String


A = Split(TextBox10.Value, ", ")
B = UserForm1.TextBox10.Value

myVar = UserForm1.TextBox10.Value

If InStr(1, myVar, ",") > 0 Then
    UserForm1.TextBox10.Value = Replace(TextBox10, ", ", ",")   'replaces any comma and space with only a comma
    UserForm1.TextBox10.Value = Replace(TextBox10, ",", ", ")   'adds space after comma for uniformity
   
End If

If Len(A(0)) <> 7 Then                                                                                           'only validates a single or first entry - needs to check all values in array
    MsgBox "Must be 7 digits, please review entry"
   
Exit Sub
End If

Appreciate any help! Thanks!

 

[Marc L]

Hi,​

​

a VBA demonstration as a beginner starter :​

Sub Demo1()
        V = InputBox(vbLf & vbLf & " Input :"):  If V = "" Then Exit Sub
    For Each V In Split(Replace(V, " ", ""), ",")
        If IsNumeric(V) Then
            If Len(V) <> 7 Then M$ = "Bad length !": Exit For
        Else
            M = "Non numeric value !":  Exit For
        End If
    Next
        If M > "" Then MsgBox M, vbExclamation, "Control"
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[kperk96]

Hi Marc,

That really helped! Thanks so much!