personnalize the value of a cell

[Guest]

ok the problem is,,,,

when you want to change the valu of your cell you can,,, and also choose a
personnalize value if you want to,,, does anyone knows how or whick one into
the list is use for postal code,,,(Canada here) ex: A1A 1A1 but do not know
which one to choose or how to create it,,, does anybody can help me on that
one??? thanks a milion

 

[Otto Moehrbach]

Sheila
Please post back and clarify what you have and what you are asking.
Write it as if you are talking to someone who has no idea what you are
talking about. HTH Otto

 

[Guest]

ok sorry,,,well ,I'm using a french version of windows xp and exel so,,, but
will do my best. try to explain,,, i wanna insert a value into a cell which
is the postal code of my client...Canada has no zip code as you do in the
US.. i know i can personnalize this value and be the one i want but do not
know how,,, so if you or anyone else can help me..... will be nice

thanks again

 

[Gord Dibben]

Sheila

Where will you be entering the postal codes?

In any particular column or row?

There is no Custom Format for Canadian Postal codes that I have found.

You can use event code in the worksheet so that as you enter the code, it will
change to A1A 1A1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
.Formula = UCase(Target.Formula)
.Value = (Left(Target.Value, 3) & " " & Right(Target.Value, 3))
End With
ErrHandler:
Application.EnableEvents = True
End Sub

As written this event code operates on Column A only.

You can type the code in as upper or lower case. Will come out as upper case no
matter what.

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.

Adjust for your column if needed.

i.e. for just column B edit to If Target.Column <> 2 Then Exit Sub


Gord Dibben MS Excel MVP..................and fellow Canuck