How to check postal code format for user form text box

I'm hoping someone can help! I'm trying to create a data entry form using a userform. I've created a text box for Postal Code. In Canada, the format is A1A 1A1. I would like to validate that the person is entering the postal code in the right format when they are using the user form. I've stumbled on how to do it within the worksheet source code (http://www.excelforum.com/excel-prog...-on-entry.html), but there must be a way to do it when I initialize the user form? I'm using Excel 2010.
Any help??

Use it as a function to evaluate valid or invalid.

Function IsValidPostCode(ByVal txt As String) As Boolean
    IsValidPostCode = Replace(txt, " ", "") Like "[A-Z][0-9][A-Z][A-Z][0-9][A-Z]"
End Function

Sorry, you've lost me.... I'm new to this...

My txt box is call txt_pc, how do I incorporate this with what you've given me!

Thanks a bunch for replying!

Something like this
To A UserForm module

Private Sub txt_pc_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.txt_pc
        If Len(.Value) Then
            If Not IsValidPostCode(.Value) Then
                MsgBox "Invalid post code", , .Value
                Cancel = True
            End If
        End If
    End With
End Sub

Function IsValidPostCode(ByVal txt As String) As Boolean
    IsValidPostCode = Replace(txt, " ", "") Like "[A-Z][0-9][A-Z][A-Z][0-9][A-Z]"
End Function

1) You need to edit your last post and wrap the vba code with the code tag.
It's a MUST rule here.
i.e
[code]
vba code here
[/code]

2) Not tested.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
If Len(txt_pc.Value) Then
    If Not IsValidPostCode(txt_pc.Value) Then
        MsgBox "Invalid entry"
        Exit Sub
    End If
End if
Set ws = Worksheets("Template")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'Validate Postal Code Format
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txt_PC.Value
'clear the data
Me.txt_PC.Value = ""
End Sub

Function IsValidPostCode(ByVal txt As String) As Boolean
    IsValidPostCode = Replace(txt, " ", "") Like "[A-Z][0-9][A-Z][A-Z][0-9][A-Z]"
End Function

Originally Posted by jindon

1) You need to edit your last post and wrap the vba code with the code tag.
It's a MUST rule here.
i.e
[code]
vba code here
[/code]

Post your workbook.

Book1.xlsm

Thanks!

Thanks Jindon.

I tried the code above, but it seems to not recognized the function IsValidPostCode. It allows me to enter the Postal Code, but it doesn't matter what I enter, it keeps coming up Invalid Entry. I'm sure it's simple, it seems so close!!!!

Any suggestions?

...................

Hi Jindon,

The xlsm file that you attached does not seem to work either. When I enter the postal code with the UserForm button in any format, it all comes back with invalid entry. The correct format should be A1A 1A1, but even when I enter that, it comes up as unvalid? Maybe I am doing something wrong?

Thanks again!

That's great Jindon! Can I see the code so that I can learn what you did?

Nope.

You must edit your post #5 first.

Sorry for my ignorance, I don't know what that means. As I said, I'm new to this, so if you could explain, that would be great!

1) Click on [Edit Post].

2) Select vba code in that post and click on "#" in the menu. It will add code tag automatically.

Private Sub CommandButton1_Click()

 Dim iRow As Long
 Dim ws As Worksheet
 Set ws = Worksheets("Template")
 

'find first empty row in database
 iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
 SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
 
'Validate Postal Code Format
 

'copy the data to the database
 ws.Cells(iRow, 1).Value = Me.txt_PC.Value
 

'clear the data
 Me.txt_PC.Value = ""
 

End Sub

Password removed.

Thanks again Jindon! One more thing, any idea how I can enforce uppercase letters?

Add this to a Form module

Private Sub txt_PC_Change()
    With Me.txt_PC
        If Len(.Value) Then .Value = UCase$(.Value)
    End With
End Sub

Hi Jindon,

One last thing, if I want to enforce the space between, would I change the function to this:

Function IsValidPostCode(ByVal txt As String) As Boolean
    IsValidPostCode = Replace(txt, " ", "") Like "[A-Za-z][0-9][A-Za-z][ ][0-9][A-Za-z][0-9]"
End Function

When I try this, it doesn't work?

Change

ws.Cells(iRow, 1).Value = txt_PC.Value

to

    ws.Cells(iRow, 1).Value = Left$(Replace$(Me.txt_PC.Value, " ", ""), 3) & _
        " " & Mid$(Replace$(Me.txt_PC.Value, " ", ""), 4)

Thank you so much Jindon for all your help and patience! I've learned so much! Hope others will see this as valuable as I did!!