Verify Canadian Postal Code ~ make the code work

[jat]

i have a code that works for a spreadsheet on it's own, but when i put the
code into an actual form, it does not work. i'm using office 2007.

in the Microsoft Excel Objects, Sheet1, i have the following:

worksheet change...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Call Postal_Verify
Application.EnableEvents = True
End If
End Sub

in the Modules, Module1, i have the following:

macro to set case if valid

Sub Postal_Verify()
Range("A1").Select
ActiveCell.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If Len(Range("A1").Text) <> 6 Then GoTo line2
If Len(Range("A1").Text) = 6 And _
IsAlpha(Mid(Range("A1").Text, 1, 1)) = True And _
IsAlpha(Mid(Range("A1").Text, 3, 1)) = True And _
IsAlpha(Mid(Range("A1").Text, 5, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 2, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 4, 1)) = True And _
IsNumeric(Mid(Range("A1").Text, 6, 1)) = True Then
Range("A1") = UCase(Left(Range("A1"), 3) & " " & Right(Range("A1"), 3))
Else
line2:
MsgBox Range("A1").Value & " - is an invalid Postcode. Canadian Postal
Codes are a six-character alpha-numeric code in the format ANA NAN, where A
represents an alphabetic characters, and N represents a numeric character."
Range("A1").ClearContents
Range("A1").Select
GoTo line1
End If
line1:
End Sub

macro to check valid alpha characters

Function IsAlpha(chr As String) As Boolean
If Asc(chr) = 100 Or Asc(chr) = 102 Or Asc(chr) = 105 Or Asc(chr) = 111 Or
Asc(chr) = 113 Or Asc(chr) = 117 _
Or Asc(chr) = 68 Or Asc(chr) = 70 Or Asc(chr) = 73 Or Asc(chr) = 79 Or
Asc(chr) = 81 Or Asc(chr) = 85 Then
MsgBox "Canadian Postal codes cannot contain the following letters: D, F, I,
O, Q, or U."
GoTo line3
End If
If Asc(chr) >= 97 And Asc(chr) <= 122 Or Asc(chr) >= 65 And Asc(chr) <= 90
Then
IsAlpha = True
Else
line3:
IsAlpha = False
End If
End Function

this works when it's in a spreadsheet on it's own after all of the
validation is done (flags invalid characters for canadian postal codes, etc.)

when i put the code into a form, it does not work. no warnings, no nothing.
the only thing that is different on the final form is the cell is not A1,
but D13:F13, and the change is made to the declared range...

any ideas to make this work...

jat

 

[jat]

A better explanation of when the code works and when it is on vacation:

The code works only if the target range (defined as A1 in this example) is
the first cell that is used, if it used after another cell (data entered into
B1, than go to A1 to enter postal code) nothing.

 

[Mike H]

Hi,

Let's try a different way. Put this in a regular module and call with

=IsCanadianzip(A1)

I didn't have a lot of real canadioan codes to test this with but looks OK,
be careful when un-linewrapping the regex pattern

Function IsCanadianzip(rng As Range)
With CreateObject("VBScript.RegExp")
.Pattern = "^([ABCEGHJKLMNPRSTVXY]\d[ABCEGHJKLMNPRSTVWXYZ])\
{0,1}(\d[ABCEGHJKLMNPRSTVWXYZ]\d)$"
If Not .test(rng.Value) Then
IsCanadianzip = "Not Valid"
Else
IsCanadianzip = "Valid"
End If
End With
End Function

Mike