Cell format 
Author Message
 Cell format

Hello everyone,

I would like to include in my database a cell format that
would enable a user to enter a Canadian postal code.
American postal codes have 5 numbers, Canadian codes
require a letter, a number, a letter (space) a number , a
letter and a final number. Looks something like this:
H9J 1B5. How do I go about creating that personnalized
format? Can we do something so that whoever is doing data
entry doesn't have to manually put a space in between and
enter the capital letters (or activate the Caps Lock)?

Thanks!



Sat, 04 Jun 2005 08:22:52 GMT  
 Cell format
You can't do this with a format because this will be a string and strings
can not be formatted with Number formats.

You would need to use the change event to insert the space.

Right click on the sheet tab and select view code.  Then paste in this code.
Change <> 2 to reflect the column where you want to enter the codes.

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

So this restricts the correction to column 2 as written.

Regards,
Tom Ogilvy


Quote:
> Hello everyone,

> I would like to include in my database a cell format that
> would enable a user to enter a Canadian postal code.
> American postal codes have 5 numbers, Canadian codes
> require a letter, a number, a letter (space) a number , a
> letter and a final number. Looks something like this:
> H9J 1B5. How do I go about creating that personnalized
> format? Can we do something so that whoever is doing data
> entry doesn't have to manually put a space in between and
> enter the capital letters (or activate the Caps Lock)?

> Thanks!



Sat, 04 Jun 2005 11:30:28 GMT  
 Cell format
I would suggest that you format the column as text so that you
can place the zip codes from any country into it.   Rather than
restricting yourself to a specific format: however, you can
use an Event macro.    I guess the space bar is hard to find
for hunt and peck typing.

You can use an Event macro to captialize  the entry and
insert a space if you really have strictly Canadian zip codes.

The following is an Event macro you can install by right
clicking on the worksheet tab,  view code,  dump the following
into the sheet module.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column <> 7 Then Exit Sub
  Dim str As String
  str = UCase(Target.Value)
  If Len(str) = 6 Then str _
     = Left(str, 3) & " " & Right(str, 3)
  Target.Value = str
End Sub

US zip codes are 5 numbers or 9 numbers (zip+4).

--
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Macros:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

Quote:

> Hello everyone,

> I would like to include in my database a cell format that
> would enable a user to enter a Canadian postal code.
> American postal codes have 5 numbers, Canadian codes
> require a letter, a number, a letter (space) a number , a
> letter and a final number. Looks something like this:
> H9J 1B5. How do I go about creating that personnalized
> format? Can we do something so that whoever is doing data
> entry doesn't have to manually put a space in between and
> enter the capital letters (or activate the Caps Lock)?

> Thanks!



Sat, 04 Jun 2005 13:58:20 GMT  
 Cell format
I forgot to disable events, corrected code below. to look more
like Tom's   <grin>.

Quote:

> I would suggest that you format the column as text so that you
> can place the zip codes from any country into it.   Rather than
> restricting yourself to a specific format: however, you can
> use an Event macro.    I guess the space bar is hard to find
> for hunt and peck typing.

> You can use an Event macro to captialize  the entry and
> insert a space if you really have strictly Canadian zip codes.

> The following is an Event macro you can install by right
> clicking on the worksheet tab,  view code,  dump the following
> into the sheet module.

  Option Explicit
  Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 7 Then Exit Sub
    Dim str As String
    str = UCase(Target.Value)
    If Len(str) = 6 Then str _
       = Left(str, 3) & " " & Right(str, 3)
      Application.EnableEvents = False
    Target.Value = str
      Application.EnableEvents = True
  End Sub
Quote:

> US zip codes are 5 numbers or 9 numbers (zip+4).

> --
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Macros:  http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm


> > Hello everyone,

> > I would like to include in my database a cell format that
> > would enable a user to enter a Canadian postal code.
> > American postal codes have 5 numbers, Canadian codes
> > require a letter, a number, a letter (space) a number , a
> > letter and a final number. Looks something like this:
> > H9J 1B5. How do I go about creating that personnalized
> > format? Can we do something so that whoever is doing data
> > entry doesn't have to manually put a space in between and
> > enter the capital letters (or activate the Caps Lock)?

> > Thanks!



Sat, 04 Jun 2005 14:03:10 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Move cell row format to column cell format

2. Color acquired from Normal Cell Format for a specific cell

3. Cell Comparison Prompts Cell Format Changes

4. How to change cell value when other cell formatting changes

5. Cell formats Don't change until the cell is active

6. Help: Cell format Type and Last Cell in UsedRange - VBA

7. Keeping cell formats during cell linking

8. Cell format based upon a result in another cell

9. Chg Cell Format, doesn't update until edit cell

10. Maintaining cell formatting with cell references


 
Powered by phpBB® Forum Software © phpBB Group