Userform textbox validation/formatting

[Mlbblue]

Hi

I hope someone can help with this.

I'd like to validate data in a textbox on exit so the user is warned that the data is not in the correct format. The validation I'm looking for is 9 numeric characters with 2 following alpha characters (i.e. 999999999XX).

I can validate so they only enter data lenth of 11 characters, but I want to force the user to enter a ref in the format above.

Any help is appreciated!!

Mark



PS. I love this message board, I spent 2 hours last night searching for tips etc. For someone like me who began learning VBA a few months ago this board is a god send!

 

[tmcfadden]

Welcome to the board Mark!

I'm sure there are better ways to do this, but here's some code that might work for you:

Function Check_mask(ByVal strTest As String, ByVal strMask As String) As Boolean
' Very simple checking mechanism
' If the character should be an integer between 0 and 9, use "0"
' If the character should be a letter, use "A"
' For example,
' 9 digits followed by two letters: "000000000AA"

Check_mask = True

If (Len(strTest) <> Len(strMask)) Then
    MsgBox "Error"
    Check_mask = False
    Exit Function
End If

Dim nLocation As Integer
For nLocation = 1 To Len(strTest)
    strT = Mid(strTest, nLocation, 1)
    strM = Mid(strMask, nLocation, 1)
    Select Case strM
        Case "0":
            If (Asc(strT) < 48 Or Asc(strT) > 57) Then
                MsgBox "Error"
                Check_mask = False
            End If
        Case "A":
            If (Asc(UCase(strT)) < 65 Or Asc(UCase(strT)) > 90) Then
                MsgBox "Error"
                Check_mask = False
            End If
    End Select
Next nLocation

End Function

It's not really pretty, but it gets the job done. You might want to modify my wickedly in-depth error messages .

-Tim

edit: Made it a function instead of a sub

 

[Mlbblue]

Great, that's worked a treat!

Cheers

Mark

 

[Norie]

Mark

You could use the Like operator.

If TextBox1.Value Like "[1-9][1-9][1-9][1-9][1-9][1-9][1-9][1-9][1-9][A-Z][A-Z]" Then
    MsgBox "Valid code."
Else
    MsgBox "Invalid code."
End If

 

[tmcfadden]

Mark

You could use the Like operator.

Much better Norie. I knew there had to be an easier way.

-Tim

 

[Norie]

Tim

I'm sure your way's fine as well, might even be better.

I think a lot of people have forgotten about the Like operator, and I'm not even sure it's available in all versions of Excel.

For some time I thought it was only available in Access.

There is another technique to do this kind of validation using regular expressions, but I think that requires referencing some other libraries.

If you want to find out more about regular expressions then try a search on the forum for RegExp.

 

[Mlbblue]

Thanks for your posts, they've been really helpful!

Mark