Excel, Cell format

Cell format

Author	Message
Greywol #1 / 4	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

Tom Ogilv #2 / 4	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

David McRitchi #3 / 4	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

David McRitchi #4 / 4	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

Page 1 of 1

[ 4 post ]