Formatting cells to deisplay a Postal Code (Canadian)

[Mister H]

Hi All:

Once again I seek the help of the Excel EXPERTS... I am doing up a spreadsheet that has Canadian Addresses on it. I want to format a group of cells to display the Postal Code in the proper format. The format is:

K2L 3W3

I want the user to be able to type in k2l3w3 and then when they tab out of the cell it displays

K2L 3W3

I would like them to be able to type in lowercase if possible. However that part I can fix by using a font that only has uppercase.

I have played arounf with the formatting but can't seem to get the results I want. If there are no letters in it it works fine but of course the letter are required.

Any suggestions?

As ALWAYS THANKS to any that can help. The Postal Code will be in Column F

Bye 4 Now,
Mark
Sudbury, Ontario
CANADA

 

[just_jon]

I think you could use a change event code

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Intersect(Target, [F:F]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">If</SPAN> Target.Row = 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">' Don't do this to the header row</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">With</SPAN> Target
    <SPAN style="color:#00007F">If</SPAN> Len(.Value) <> 6 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Error: invalid code!"
    <SPAN style="color:#00007F">Else</SPAN>
        .Value = UCase(Left(.Value, 3)) & " " & UCase(Right(.Value, 3))
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Been through Sudbury, pretty place.

EDIT Revised code to tag row 1 instead of computing range from F2...

 

[Mister H]

Hi just_jon:

THANKS for the code. However I can't seem to get it to do anything. I am not overly familar with the VBA in Excel so I am sure it is pronbably just me not following your direction.

I copied the code and then right clicked on the sheet and selected View Code. I pasted it there. Is that correct? When I went and typed something into column F nothing happend when I tabbed out. Did I do something wrong? I am not sure what you are referring to here (sorry):

EDIT Revised code to tag row 1 instead of computing range from F2...

I did try changing the F:F in your code to F2:F1000 but still got no results. Any direction you can provide would be great. I have only been in Sudbury for a year now but I to find it nice.

Bye 4 Now,
Mark

 

[just_jon]

Hi just_jon:

THANKS for the code. However I can't seem to get it to do anything. I am not overly familar with the VBA in Excel so I am sure it is pronbably just me not following your direction.

I copied the code and then right clicked on the sheet and selected View Code. I pasted it there. Is that correct? When I went and typed something into column F nothing happend when I tabbed out. Did I do something wrong? I am not sure what you are referring to here (sorry):

EDIT Revised code to tag row 1 instead of computing range from F2...

I did try changing the F:F in your code to F2:F1000 but still got no results. Any direction you can provide would be great. I have only been in Sudbury for a year now but I to find it nice.

Bye 4 Now,
Mark

Well, no wonder! You did nothing wrong, it was I... just change the next to last tine from

Application.EnableEvents = False

to

Application.EnableEvents = True

 

[Mister H]

Hello AGAIN:

SORRY to keep bugging you but I still can't seem to get it to work. I tried formmatting column F as Test but still nothing. Here is the code I pasted into the View Code for sheet 1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [F2:F100]) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub ' Don't do this to the header row
Application.EnableEvents = False
With Target
If Len(.Value) <> 6 Then
MsgBox "Error: invalid code!"
Else
.Value = UCase(Left(.Value, 3)) & " " & UCase(Right(.Value, 3))
End If
End With
Application.EnableEvents = True
End Sub

I also tried it with just F:F instead of F2:F100 but still no results when tabbing out of the cell. I REALLY do need to learn the VBA aspect of Excel. I am sure that will make life much easier and more interesting.

Take Care,
Mark

 

[just_jon]

Do this - put this right below the other code

Sub just_jonScrewedUp
Application.EnableEvents = True
End Sub

Then Click Tools | Macro | Macros, select it and click Run

What has happened, you loaded my original [ wrong ] code and ran it, which left Events disabled.

Run this new code once, then you can delete it.

 

[Mister H]

THANKS just_jon... I am now up and running. I VERY MUCH APPRECIATE your help and guidance.

Have a GREAT day,
Mark