postcodes

hi, i am validating a postcode using this code, which puts the space in for the user, but what i really wanted to do was check each letter or use regex but i have never used regex before and i dont know how to check each letter.
the reason i need this is because the user can just enter 1234567 and it is validated fine, any ideas or good tutorials??

Code:

---

Private Sub TextBox9_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox9.Text = Trim(TextBox9.Text)

Dim strPhone As String
strPhone = Replace(TextBox9.Text, " ", "")

If Len(strPhone) = 6 Or 7 Then
strPhone = Mid(strPhone, 1, 4) & " " & Mid(strPhone, 5, Len(strPhone) - 4)
TextBox9.Text = strPhone

Else
MsgBox TextBox6.Text & " is not a valid postcode. Please enter a valid postcode.", vbOKOnly, "Invalid postcode"
TextBox9.Text = ""
TextBox9.SetFocus
Cancel = True

End If

End Sub

---

thanks
joe

If its an app with internet access you can connect to some of the free online zip code databases in order to verify the code.

nope its just an excel user input form.

pain in the bum post codes
as they could be two letters then a number or one letter

you could check to see if the first character is numeric
if it is then ask them to reinput

how would i check that the first letter isnt numeric?

vb Code:

Sub test()
 
Dim string1 As String
 
string1 = "12345"
 
If IsNumeric(Left(string1, 1)) Then
    MsgBox "Your postcode does not start with a letter"
    Exit sub
End If
 
End Sub

Hi

Here is another thing that you can do... (It is just a suggestion)

Assuming that you are from UK, What I suggest that you google for a list of postcodes. As per this website there are 2821 postcodes, which you can copy and paste in one of your sheets in excel. I believe the file which this website offers is not working so you can search for any other site. I am sure that there are plenty of them... You can then use this data to compare/validate with the data that the user inputs...

If you are from any other country, then you can google for that country's postcode and use them...

I think that this might be the most simplest way to do it... I could be wrong...

that sounds like a good solution. what code do i use to match the users input to my list of postcodes?

i cant seem to find a list of all the total postcodes, but what i have found is a table that displays the area code and which city it stands for, e.g. ng stand for nottingham etc.
is there any way that i could check that the city the user has entered and the postcode he has entered match up using this table.
as this would give me half the validation and then just check for length to see if the postcode is the right length.

any ideas or code?

thanks

you'd first have to check the second character of the entered post code
see if it is numeric, if it is then you check the first character to the post code list
if the second character isnt numeric, you would then check the postcode using the two characters

if that makes sense :s :)

Quote:

---

i cant seem to find a list of all the total postcodes

---

Let me see if i can get you the list of post codes. can you confirm which country you want the post codes for?

all Uk postcodes please

thanks

Does this help?

http://www.kubelabs.com/ukpostcodedata.php

See right at the bottom of that page or click here

so... using this big list of postcodes and where they are from, how would i compare it to my user inputted post code and city/town.

let me write a short code for you :)

could you explain it then as you go, as i would like to learn how to do it for myself.. you wont always be here to help..

or if you could give me some things to look up on google i could have a go my self.

Hi Joe

Here is a very basic example to do the check. I have checked it and it works...

Please edit the code as per your requirements...

vb Code:

Private Sub CommandButton1_Click()
 
Dim srch As String
Dim C As Range
Dim Flag As Boolean
 
'Flag to check if postcode is correct
Flag = False
 
'Say you want the user to input the postcode
'in Cell D2
'If it is say a "Textbox1" then assign the Textbox1.value
'to "srch"
srch = Trim(Range("D2").Value)
 
'As per the CSV File DATA is from A2 to A3293
'Replace Sheet2 with the name of the sheet
'Where the Data is stored...
For Each C In Sheets("Sheet2").Range("A2:A3293")
    If Trim(C.Value) = srch Then
        'postcode found
        Flag = True
        Exit For '<===== [EDIT]: I added this line
    End If
Next C
 
If Flag = False Then
    'or what ever message
    MsgBox "Please enter a valid postcode"
Else
    'or what ever message
    MsgBox "It is a correct postcode"
End If
 
End Sub

Hope this helps...

would this work if i put it in the before update event of a textbox??

I don't see a reason why it shouldn't :)

I have commented the code so you understand what the code does... Please edit the above code as per your requirements.

ok, thankyou i am just trying it know in the before update event,

i tried it using this code,

Code:

---

Private Sub TextBox9_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim srch As String
Dim C As Range
Dim Flag As Boolean
'Flag to check if postcode is correct
Flag = False
'Say you want the user to input the postcode
'in Cell D2
'If it is say a "Textbox1" then assign the Textbox1.value
'to "srch"
srch = Trim(TextBox9.Value)
'As per the CSV File DATA is from A2 to A3293
'Replace Sheet2 with the name of the sheet
'Where the Data is stored...
For Each C In Sheets("Sheet3").Range("A2:A3293")
If Trim(C.Value) = srch Then
'postcode found
Flag = True
End If
Next C
If Flag = False Then
'or what ever message
MsgBox "Please enter a valid postcode"
TextBox9.Text = ""
TextBox9.SetFocus
Cancel = True
Else
'or what ever message


End If

End Sub

---

but even when i put in a proper postcode e.g. ng14 5bn, my postcode

i still get a mesage box asking me to input a valid postcode???

Quote:

---

but even when i put in a proper postcode e.g. ng14 5bn, my postcode

---

That is because, you don't have that postcode in that CSV list...

Try it with any postcode from that list...

Also while entering the postecode use Uppercase for the time being

or change this line

vb Code:

If Trim(C.Value) = srch Then

to

vb Code:

If ucase(Trim(C.Value)) = ucase(srch) Then

Hope this helps...

how else could i validate then using just the list of area codes and citys as i need it to accept all postcodes and the list dosnt have all the postcodes in.
i tried putting in the first postcode on the list and it still didnt accept??

what i need is for the code to check the first part of the user inputted postcode against the list and the user inputted city aginst the list and if they are on the same row then the postcode will be pretty much right, and then check its length, if this is right aswell it will be accepted.

the list i am on about can be found here

any ideas or help is much appreciated

Joe

you could always insert a combo box so that the user selects the prefix for the postcode....
that way you know its valid....

or i could fill in the first part of the postcode for them depending on what city they put in, this could be a lebel so they coudnt chage it... would this work??

i would still rather allow them to fill in the whole postcode themselves though:)

London wouldnt work though mate
as it would depend on the area

Quote:

---

what i need is for the code to check the first part of the user inputted postcode against the list and the user inputted city aginst the list and if they are on the same row then the postcode will be pretty much right, and then check its length, if this is right aswell it will be accepted.

---

Can you mail me the file? :)

Let me see what do you exacty have?

You still have my email address? Correct?

ok, sent it.

thanks

Hello,

No need for a list of post Codes all though It would be ideal although imposible to keep updated.

here is a Sub that will validate the UK post code for you.

Code:

---

Function IsPostcode(Postcode As String) As Boolean

Dim sPostcodeLeft As String
Dim sPostcodeRight As String

Dim Office As String 'First one or two Letters
Dim District As String 'One or two numbers, or one number and one letter
Dim Sector As String 'Usually one number (expect variations)
Dim UnitCode As String 'Always two letters

IsPostcode = False

If Len(Postcode) < 5 Then
    Exit Function
End If

Postcode = UCase(Postcode)

On Error GoTo EndFunction

If Len(Postcode) <> 8 Then
    sPostcodeRight = Trim(Right(Postcode, 3))
    sPostcodeLeft = Trim(Left(Postcode, Len(Postcode) - 3))
    Do Until Len(sPostcodeLeft) > 4
        sPostcodeLeft = sPostcodeLeft & " "
    Loop
    Postcode = UCase(sPostcodeLeft & sPostcodeRight)
End If

sPostcodeRight = Trim(Right(Postcode, 3))
sPostcodeLeft = Trim(Left(Postcode, Len(Postcode) - 3))

If Len(sPostcodeRight) = 3 Then
    Sector = Left(sPostcodeRight, 1)
    UnitCode = Right(sPostcodeRight, 2)
    If Not (Asc(Mid(UnitCode, 1, 1)) >= 65 And Asc(Mid(UnitCode, 1, 1)) <= 90) Then
        Exit Function
    End If
    If Not (Asc(Mid(UnitCode, 2, 1)) >= 65 And Asc(Mid(UnitCode, 2, 1)) <= 90) Then
        Exit Function
    End If
Else
    Exit Function
End If

If Len(sPostcodeLeft) >= 2 And Len(sPostcodeLeft) <= 4 Then
    If Asc(Left(sPostcodeLeft, 1)) >= 65 And Asc(Left(sPostcodeLeft, 1)) <= 90 Then
        If (Asc(Mid(sPostcodeLeft, 2, 1)) >= 65 And Asc(Mid(sPostcodeLeft, 2, 1)) <= 90) Then
            Office = Left(sPostcodeLeft, 2)
        ElseIf (Asc(Mid(sPostcodeLeft, 2, 1)) >= 48 And Asc(Mid(sPostcodeLeft, 2, 1)) <= 57) Then
            Office = Left(sPostcodeLeft, 1)
        Else
            Exit Function
        End If
        District = Right(sPostcodeLeft, Len(sPostcodeLeft) - Len(Office))
        If Not (Asc(Left(District, 1)) >= 48 And Asc(Left(District, 1)) <= 57) Then
            Exit Function
        Else
            If Len(District) = 2 Then
                If Not (Asc(Mid(District, 2, 1)) >= 48 And Asc(Mid(District, 2, 1)) <= 57) And Not (Asc(Mid(District, 2, 1)) >= 65 And Asc(Mid(District, 2, 1)) <= 90) Then
                    Exit Function
                End If
            ElseIf Len(District) = 1 Then
                If Not (Asc(District) >= 48 And Asc(District) <= 57) Then
                    Exit Function
                End If
            Else
                Exit Function
            End If
        End If
    Else
        Exit Function
    End If
Else
    Exit Function
End If

IsPostcode = True

EndFunction:
End Function

---

thanks
Dav

i just seem to get a load of errors when i use this, what code would i put in the before update event of my textbox to call the function?? as this is probs what i am doing wrong.

thanks joe

any ideas anyone, thanks alot Joe

OK, to validate it from the list in post 14, all you need to do is split off the last 3 characters, because all UK postcode end in 3 characters, then check what's left against the list. If you want to validate the last 3 as one number, two letters, then that is a very straightforward addition.

For now:

VB Code:

Dim c As Range
 
Dim MyPostCode As String
Dim PostCodeStart As String
Dim PostCodeEnd As String
 
 
MyPostCode = UCase(Range("C8").Value)   'Make it upper case
 
'UK postcode always ends in 3 characters. Beginning part is variable, and is whatever is left.
PostCodeStart = Left$(MyPostCode, Len(MyPostCode) - 3)
PostCodeEnd = Right$(MyPostCode, 3)
 
 
'Find it in the list on sheet 2
 
Set c = Worksheets("Sheet2").Range("A:A").Find(PostCodeStart, LookIn:=xlValues, LookAt:=xlWhole)
 
If Not (c Is Nothing) Then      'Found it
    
    MsgBox "Postcode: " & PostCodeStart & " " & PostCodeEnd & Chr(13) & "Location: " & c.Offset(0, 1)
 
Else        'Not found
 
    GoTo InvalidPostCode:
 
End If
 
 
Exit Sub
 
'Error message
 
InvalidPostCode:
 
MsgBox "Please enter a valid postcode"

Put this in a commandbutton on the form, put a postcode in cell C8 and copy the list of postcodes into columns A to D on sheet 2.


Then all you need to do is change the input from Range("C8") to to whatever, and off you go.


zaza

the post code is being entered into a textbox on a form though, not a cell. And i would it to check the postcode before the user can leave the focus of the textbox as this is how all my other validation works. any ideas?

But that's wholly incidental. Instead of taking your input from the cell, take it from textbox1.text or whatever. And set it to trigger on the Exit event of the textbox.

Come on, you might have to do a little bit of adapting to your specific needs here.