Private Sub TextBox8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Const strFirst As String = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,R,S,T,U,W,Y,Z"
Const strSecond As String = "A,B,C,D,E,F,G,H,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y"
Const strThird As String = "A,B,C,D,E,F,G,H,J,K,S,T,U,W"
Const strLast As String = "A,B,D,E,F,G,H,J,L,N,P,Q,R,S,T,U,W,X,Y,Z"
Dim vChar As Variant
Dim iChar As Integer
Dim strPC As String
Dim bFound As Boolean
strPC = Trim(TextBox8.Text)
TextBox8.Text = Trim(UCase(strPC))
If Len(strPC) = 0 Then
TextBox8.BackColor = &H80000005
Exit Sub
End If
'Too few characters
If Len(strPC) > 0 And Len(strPC) < 6 Then
MsgBox "Post code is invalid - too few characters!"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
'Too many characters
If Len(strPC) > 8 Then
MsgBox "Post code is invalid - too many characters!"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
'Correct for no space in post code
If InStr(1, strPC, Chr(32)) = 0 Then
TextBox8.Text = UCase(Left(strPC, Len(strPC) - 3) & Chr(32) & Right(strPC, 3))
End If
'Check the strings according to their length
Select Case Len(TextBox8.Text)
Case 6
vChar = Split(strFirst, Chr(44))
bFound = False
For iChar = 0 To UBound(vChar)
If Left(TextBox8.Text, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode first character is invalid"
Exit Sub
End If
bFound = False
If Not IsNumeric(Mid(TextBox8.Text, 2, 1)) Then
MsgBox "PostCode second character should be a number"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
If Not IsNumeric(Mid(TextBox8.Text, 4, 1)) Then
MsgBox "PostCode fourth character should be a number"
Exit Sub
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
End If
bFound = False
vChar = Split(strLast, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 5, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode penultimate character is invalid"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
bFound = False
vChar = Split(strLast, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 6, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode last character is invalid"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
Case 7
vChar = Split(strFirst, Chr(44))
bFound = False
For iChar = 0 To UBound(vChar)
If Left(TextBox8.Text, 1) = vChar(iChar) Then
bFound = True
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode first character is invalid"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
bFound = False
If Not IsNumeric(Mid(TextBox8.Text, 2, 1)) Then
vChar = Split(strSecond, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 2, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
Else
bFound = True
End If
If Not bFound Then
MsgBox "PostCode second character is invalid"
Exit Sub
End If
bFound = False
If Not IsNumeric(Mid(TextBox8.Text, 3, 1)) Then
vChar = Split(strThird, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 3, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
Else
bFound = True
End If
If Not bFound Then
MsgBox "PostCode third character is invalid"
Exit Sub
End If
If Not IsNumeric(Mid(TextBox8.Text, 5, 1)) Then
MsgBox "PostCode fourth character should be a number"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
bFound = False
vChar = Split(strLast, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 6, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode penultimate character is invalid"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
bFound = False
vChar = Split(strLast, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 7, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode last character is invalid"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
Case 8
vChar = Split(strFirst, Chr(44))
bFound = False
For iChar = 0 To UBound(vChar)
If Left(TextBox8.Text, 1) = vChar(iChar) Then
bFound = True
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode first character is invalid"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
bFound = False
vChar = Split(strSecond, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 2, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode second character is invalid"
Exit Sub
End If
If Not IsNumeric(Mid(TextBox8.Text, 3, 1)) Then
MsgBox "PostCode third character should be a number"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
bFound = False
If Not IsNumeric(Mid(TextBox8.Text, 4, 1)) Then
vChar = Split(strThird, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 4, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
Else
bFound = True
End If
If Not bFound Then
MsgBox "PostCode fourth character is invalid"
Exit Sub
End If
If Not IsNumeric(Mid(TextBox8.Text, 6, 1)) Then
MsgBox "PostCode sixth character should be a number"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
bFound = False
vChar = Split(strLast, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 7, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode penultimate character is invalid"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
bFound = False
vChar = Split(strLast, Chr(44))
For iChar = 0 To UBound(vChar)
If Mid(TextBox8.Text, 8, 1) = vChar(iChar) Then
bFound = True
Exit For
End If
Next iChar
If Not bFound Then
MsgBox "PostCode last character is invalid"
TextBox8.SetFocus: TextBox8.BackColor = &HC993FF
Exit Sub
End If
End Select
TextBox8.BackColor = &HC0F799
End Sub