| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verify Canadian Postal Code ~ make the code work
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Verify Canadian Postal Code ~ make the code work
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. "jat" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Verify Canadian Postal Code ~ make the code work
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 "jat" wrote: 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. "jat" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ?Validate Canadian Postal Code | Excel Programming | |||
| Canadian Postal Code format? | Excel Worksheet Functions | |||
| formula for Canadian postal codes | Excel Worksheet Functions | |||
| formula for Canadian Postal Codes | Excel Worksheet Functions | |||
| Canadian Postal Code | Excel Programming | |||
Linear Mode
