Format Canadian Postal Code on Entry

Originally Posted by mudraker

Gos-C

Try this macro - It does not verify Letters & Number are placed in the correct order - This can be added if required.

Copy this macro
Goto Excel
Right Click on Sheet Name Tab > select View Code
Past macro into the Worksheet Module displayed

Change Column number to suit (shown In red in this posting)
Return to Excel and test

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rng As Range
   
   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
      End If
   Next Rng
   Application.EnableEvents = True
End Sub

Hi Mudraker,

This work is fantastic and thank you in advance on behalf of all Canadian excel users who have been loosing sleep in trying to automate the post code entry/ format process!

I am a Excel 2011 for Mac user and when I right click on the Sheet Tab, I do not have an option to View Code. I tried pasting the code into the macro record window but now every entry in the sheet comes up with an 'Invalid Postal Code' error.

Could you please offer some (more!) guidance?

Does anyone know if I can use this code to force postal code entry in a user form text box?