Visual Basic for Applications/Validate with the Like Operator

Summary

This VBA example can be run in any of the commonly used Microsoft Office applications. The examples on this page make use of the Like operator to compare strings. The first example shows how to check that a string conforms to the correct format of the United Kingdom's National Insurance Number (NINO), a number used in much the same way as the US Social Security Number (SSN). The rules for the format are clear so it makes a good example. Unlike examples elsewhere in this set that check for illegal characters while they are being entered, this method is carried out only when the user has completed the entry.

The VBA Code

The code needs a user form called Userform1, two text boxes, TextBox1 and TextBox2, and a command button called CommandButton1. Set the UserForm1 property ShowModal to false for convenient study. Copy the code below into the three respective modules and save the workbook with an xlsm file suffix.
When the workbook is opened, the user form will be displayed. Type a number format into TextBox1 and when complete, press the tab key to move to the next textbox. If the number format is correct then the insertion point will move, but if not it will stay in the faulty text ready for correction. Setting the Cancel argument of BeforeUpdate() to true prevents the move.
Note that the Before_Update() event will not run at all unless a change has been made the text since the last time that the insertion point entered the box. So, to labor the point, after leaving the box, if the user clicks in it again without changes, the event does not run when moving on. If this poses a problem then consider the use of the Exit event for testing.
See also Input Boxes for a number of other validation-related procedures.

Code Changes

There are no changes so far.

For the ThisWorkbook Module

Private Sub Workbook_Open()
    ' Runs when workbook opens
    
    Load UserForm1
    UserForm1.Show

End Sub

For the UserForm1 Module

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    'Runs on exit from the textbox provided that changes to text were made.
    'Setting Cancel to True keeps the insertion point in the text
    'instead of tabbing on.
    
    If IsNINO(TextBox1.Value) Then
        'all ok
    Else
        Cancel = True
    End If

End Sub

For the Standard Module

Sub testIsNINO()
    'run this to test the IsNINO procedure
    
    Dim sIn As String
    
    'set nino here to test
    sIn = "QQ123456A"
    
    MsgBox IsNINO(sIn)

End Sub

Function IsNINO(sIn As String) As Boolean
    ' Checks format of UK National Insurance Number (NINO)
    ' Converts to upper case for comparison
   
   'NOTES: Ref:National Insurance Numbers (NINOs): Format and Security:
   '       https://www.gov.uk/hmrc-internal-manuals/national-insurance-manual/nim39110
   'A NINO is made up of two letters, six numbers and a final letter, which is always A, B, C, or D.
   'D, F, I, Q, U, and V are not used as first or second letter of prefix.
   'Letter O is not used as the second letter of prefix.
   'Prefix combinations BG, GB, KN, NK, NT, TN and ZZ are not to be used.
   'Suffix characters can be only A,B, C,or D. (Elsewhere in examples space has been included here.)
       
    Dim bTemp As Boolean
    Const s1 As String = "[ABCEGHJKLMNOPRSTWXYZ]" 'alphabet less D, F, I, Q, U, and V; pattern for the first letter
    Const s2 As String = "[ABCEGHJKLMNPRSTWXYZ]"  'alphabet less D, F, I, O , Q, U, and V; pattern for the second letter
    Const s3 As String = "######"                 'includes only six integers; pattern for the six integers
    Const s4 As String = "[ABCD]"                 'includes only A, B, C, or D; pattern for the end letter
    
    ' Four parts of number to check are each in square brackets
    ' Right hand side of like operation concatenates
    ' all four pattern strings as one. Notice that the alpha patterns here make
    ' use of long format notation where every character permitted has been included.
    ' Instead, the alpha patterns could have been expressed as ranges; eg; "[ABCD]" is same as "[A-D]"
    bTemp = UCase(sIn) Like s1 & s2 & s3 & s4

    If bTemp Then
        ' Check for illegal pairs
        Select Case Left$(UCase(sIn), 2)
            Case "BG", "GB", "KN", "NK", "NT", "TN", "ZZ"
                IsNINO = False
                MsgBox "Illegal prefix pair detected."
                Exit Function
            Case Else
                IsNINO = True
                Exit Function
        End Select
    Else
        MsgBox "Illegal characters detected."
        IsNINO = False
        Exit Function
    End If

End Function

External Links