Format Canadian Postal Code on Entry

I should have been a bit more thourgh on the testing before posting the code

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Dim iChar As Integer
   
   For Each Rng In Target
      If Rng.Column = 1 Then
         Application.EnableEvents = False
         Select Case Len(Rng.Value)
         Case 6
            Rng.Value = UCase(Left(Rng.Value, 3) & " " & Right(Rng.Value, 3))
         Case 7
            Rng.Value = UCase(Rng.Value)
         End Select
         If Len(Rng.Value) <> 7 Then
            MsgBox "Invalid Post Code Entyry in " & Rng.Address
            Exit For
         End If
         For iChar = 1 To 7 Step 1
            Select Case Mid(Rng.Value, iChar, 1)
            Case "A" To "Z"
               Select Case iChar
               Case 2, 4, 5, 7
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case 0 To 9
               Select Case iChar
               Case 1, 3, 4, 6
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                  & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case " "
               Select Case iChar
               Case 1 To 3, 5 To 7
                  MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                     & " is invalid for Postcode"
                  'rng.ClearContents
                  Exit For
               End Select
            Case Else
               MsgBox Rng.Address & " - " & Mid(Rng.Value, iChar, 1) _
                     & " is invalid for Postcode"
                  'rng.ClearContents
               Exit For
            End Select
         Next iChar
      End If
   Next Rng
   Application.EnableEvents = True
End Sub

Glad to hear all is now working correctly & thanks for the feed back

I beleive this might also helpful if you are entering Postcode in Range 'A1'.

Sub Postal_Verify()
If Len(Range("A1").Text) = 6 And _
IsAlpha(Mid(Range("A1").Text, 1, 1)) = True And _
IsAlpha(Mid(Range("A1").Text, 3, 1)) = True And _
IsAlpha(Mid(Range("A1").Text, 5, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 2, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 4, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 6, 1)) = True Then
Range("A1") = Left(Range("A1"), 3) & " " & Right(Range("A1"), 3)
Else
MsgBox "Invalid Postal Code."
End If
End Sub
Function IsAlpha(chr As String) As Boolean
If Asc(chr) >= 97 And Asc(chr) <= 122 Or Asc(chr) >= 65 And Asc(chr) <= 90 Then
IsAlpha = True
Else
IsAlpha = False
End If
End Function

Regards,
Gajendra Gupta

This is even more simpler..

Sub Postal_Verify()
If Range("D1") Like "[A-Za-z][0-9][A-Za-z][0-9][A-Za-z][0-9]" Then
Range("A1") = Left(Range("A1"), 3) & " " & Right(Range("A1"), 3)
Else
MsgBox "Invalid Postal Code."
End If
End Sub

Regards,
Gajendra Gupta

Gajendra

Fantastic bit of code - I would never have thought of using Like

I have modified my macro to incorporate your code.

The new macro retains all the requirements as per Gos-C 1st posting and is much simpler than what I had coded

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   Dim iChar As Integer
   
   For Each Rng In Target
      If Rng.Column = 1 Then
         Application.EnableEvents = False
         Select Case Len(Rng.Value)
         Case 6
            Rng.Value = UCase(Left(Rng.Value, 3) & " " & Right(Rng.Value, 3))
         Case 7
            Rng.Value = UCase(Rng.Value)
         End Select
         If Rng.Value Like "[A-Z][0-9][A-Z][ ][0-9][A-Z][0-9]" Then
            'do nothing
         Else
            MsgBox Rng.Address & " - invalid Postcode"
         End If
      End If
   Next Rng
   Application.EnableEvents = True
End Sub