Format Canadian Postal Code on Entry

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

Gos-C

Contents now cleared on invalid postcodes - New line shown in red

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"
            Rng.ClearContents
         End If
      End If
   Next Rng
   Application.EnableEvents = True
End Sub

Hello,

I faces the same problem where i have a column of postal code and some of them are invalid. However, for some reason, I cannot run the macro. Nothing happened. I wonder why?

Just applied this to a project - still working in 2015! Thanks!

Hi Gos-C,

I found the code and have inserted it (Excel 2010). The odd thing though is that the postal code is repeated five times. I hope the attached image is visible so you can see what I mean. Any ideas? Thanks for any help that you can provide!

Chris

PostalCode.JPG