Userform textbox validation/formatting

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20
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!! :biggrin:

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! :biggrin:
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
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:
Code:
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Mark

You could use the Like operator.
Code:
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

Board Regular
Joined
Aug 17, 2005
Messages
151

ADVERTISEMENT

Mark

You could use the Like operator.

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

-Tim
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,310
Messages
5,675,002
Members
419,543
Latest member
Casp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top