Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Formatting (Excel 2002)

    There is a special format available for Zip Codes and Zip Codes + 4. I would like to add a custom format for our Canadian Postal Code which takes the format of Letter, number, letter, space, number, letter, number (A0A 0A0). Is there any way to set up a custom cell format for this?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cell Formatting (Excel 2002)

    The problem is, because of the mixture of text and numbers Excel "sees" the entry as text and leaves it alone.
    Let me think about this...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting (Excel 2002)

    What exactly are you trying to accomplish? Formats are only for numbers. If your postal codes contain letters, then they will be text and no format will be used. Text will be displayed exactly as entered, so if you enter the postal code in the correct format, what is there to do?
    Legare Coleman

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cell Formatting (Excel 2002)

    Thought about it - this is what I came up with:
    <pre>Sub GoPostal()
    'formats Candian Postal Codes
    Dim Rng As Range
    For Each Rng In Selection.Cells
    If Rng.HasFormula = False Then
    Rng.Value = StrConv(Rng.Value, vbUpperCase)
    Rng.Value = Left(Rng.Value, 3) & " " & Right(Rng.Value, 3)
    End If
    Next Rng
    End Sub
    </pre>


    This will put the postal code in uppercase and ensure that there is a space between the two portions of the postal code - it will not flag errors <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> (that would be interesting to try).
    I'd attach it to a button for quick and easy use. If you have any questions, just post again.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting (Excel 2002)

    I would like to ensure that the postal codes ARE entered correctly - and consistently appear with the appropriate space. If formats apply exclusively to numbers, then I may be looking in the wrong place. I'm looking for something similar to the Input Mask that's available in MS Access. Is there any other way to validate entries?

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting (Excel 2002)

    That's great - I'll try it (I especially like the name of your routine!). Thanks for your help.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting (Excel 2002)

    OK, the code below, placed in the change event routine for the worksheet where the postal codes will be entered, will catch invalid codes when they are entered and display a message to the user. If the code is correct, it will put a space where it should be. This code assumes that the postal codes will be entered in Column A. If that is not the correct column, then the code will need to be modified in the two places where you see Range("A:A").

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim StrPC As String, I As Integer, bPCOK As Boolean
    If Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
    If oCell.Value <> "" Then
    If Len(oCell.Value) < 6 Or Len(oCell.Value) > 7 Then
    oCell.Value = ""
    oCell.Select
    MsgBox "Invalid Postal Code in cell " & oCell.Address & " deleted."
    Else
    StrPC = UCase(Left(oCell.Value, 3) & Right(oCell.Value, 3))
    bPCOK = True
    For I = 1 To 6
    If (I = 1 Or I = 3 Or I = 5) And IsNumeric(Mid(StrPC, I, 1)) Then
    oCell.Value = ""
    oCell.Select
    MsgBox "Invalid Postal Code in cell " & oCell.Address & " deleted."
    bPCOK = False
    Else
    If (I = 2 Or I = 4 Or I = 6) And Not IsNumeric(Mid(StrPC, I, 1)) Then
    oCell.Value = ""
    oCell.Select
    MsgBox "Invalid Postal Code in cell " & oCell.Address & " deleted."
    bPCOK = False
    End If
    End If
    Next I
    If bPCOK Then
    oCell.Value = Left(StrPC, 3) & " " & Right(StrPC, 3)
    Else
    oCell.Value = ""
    End If
    End If
    End If
    Next oCell
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cell Formatting (Excel 2002)

    Interesting - I was trying to use the code function to return the values, as not all letters of the alphabet are valid and only numbers between 0 and 9 are valid.
    But the code function doesn't seem to have a match in VBA, closest I could find is the keyboardconstants, which look like they could return a value.

    or I am I chasing in circles here....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Formatting (Excel 2002)

    The function you want is Asc().
    Legare Coleman

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cell Formatting (Excel 2002)

    YOU are amazing!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cell Formatting (Excel 2002)

    With thanks to Legare for getting me back on the right track.
    <pre>Sub PostalSupreme()
    'formats and checks correctness for postal codes
    Dim Rng As Range
    Dim FirstPos, SecondPos, ThirdPos, FourthPos, FifthPos, SixthPos, SeventhPos As String
    Dim BadCode As Boolean


    For Each Rng In Selection.Cells
    If Rng.HasFormula = True Then
    BadCode = True
    Else
    Rng.Value = StrConv(Rng.Value, vbUpperCase)
    Rng.Value = Left(Rng.Value, 3) & " " & Right(Rng.Value, 3)
    End If
    Next Rng

    For Each Rng In Selection.Cells

    ' Check the length
    If Len(Rng.Value) = 7 Then
    'assign values to each position
    FirstPos = Left(Rng.Value, 1)
    SecondPos = Mid(Rng.Value, 2, 1)
    ThirdPos = Mid(Rng.Value, 3, 1)
    FourthPos = Mid(Rng.Value, 4, 1)
    FifthPos = Mid(Rng.Value, 5, 1)
    SixthPos = Mid(Rng.Value, 6, 1)
    SeventhPos = Mid(Rng.Value, 7, 1)
    ' convert to code values
    FirstPos = Asc(FirstPos)
    SecondPos = Asc(SecondPos)
    ThirdPos = Asc(ThirdPos)
    FourthPos = Asc(FourthPos)
    FifthPos = Asc(FifthPos)
    SixthPos = Asc(SixthPos)
    SeventhPos = Asc(SeventhPos)

    'code values 81, 68, 70, 73, 79, 85, 87 and 90 correspond to
    ' Q, D, F, I, O, U, W and Z which I believe do not appear anywhere in
    ' Canadian postal codes

    ' checks the first, third and sixth positions for Capital Letters
    Select Case FirstPos
    Case 65 To 90
    Select Case FirstPos
    Case 81, 68, 70, 73, 79, 85, 87, 90
    BadCode = True
    Case Else
    Select Case ThirdPos
    Case 65 To 90
    Select Case ThirdPos
    Case 81, 68, 70, 73, 79, 85, 87, 90
    BadCode = True
    Case Else
    Select Case SixthPos
    Case 65 To 90
    Select Case SixthPos
    Case 81, 68, 70, 73, 79, 85, 87, 90
    BadCode = True
    End Select
    End Select
    End Select
    End Select
    End Select
    Case Else
    BadCode = True
    End Select
    ' numbers and space ie second, fourth, fifth and seventh positions
    Select Case SecondPos
    Case 48 To 57
    Select Case FourthPos
    Case Is = 32
    Select Case FifthPos
    Case 48 To 57
    Select Case SeventhPos
    Case 48 To 57
    Case Else
    BadCode = True
    End Select
    Case Else
    MsgBox BadCode = True
    End Select
    Case Else
    BadCode = True
    End Select
    Case Else
    BadCode = True
    End Select

    Else
    BadCode = True

    End If

    If BadCode = True Then
    On Error Resume Next
    With Rng.AddComment
    .Visible = True
    .Text Text:="Bad Postal Code"
    End With
    End If

    Next Rng


    End Sub
    </pre>


    The code checks for the correct alpha numeric format ie ANA NAN, it also checks for the presence of some letters which I believe are not used in Canadian postal codes ie: D, F, I, Q, O, U, W and Z. (Any other Canadian loungers who know more about Postal code rules than I, feel free to enlighten me). It places a comment indicating a bad postal code in the cell.

    When you are testing this out you might want this one as well it will strip comments out of the selected range:
    <pre>Sub removecomments()
    Dim Rng As Range

    For Each Rng In Selection.Cells
    Rng.ClearComments
    Next Rng

    End Sub
    </pre>


    Legare do you have any suggestions on condensing the code?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •