postcode format Netherlands

[wimven]

Please,

Can someone help me to the VBA lines for the entry field txtPostcd in a userform which should give the format:

4 digits, space, 2 letters

 

[Andrew Poulsom]

Do you get invalid with 1234 AB? I don't.

 

[wimven]

still no luck but i feel that this should be able to work.

On the worksheet i have a button which launches the macro "newclient":

Sub NewClient()
FrmNewClient.Show
End Sub

The form:
Private Sub UserForm_Initialize()
Dim maxvalrange, maxval
Set maxvalrange = Range("Clients!q2:q10000")
maxval = Application.WorksheetFunction.Max(maxvalrange)

TxtSurname = ""
TxtAttn = ""
TxtStreet = ""
TxtStrNr = ""
TxtPostcd = ""
TxtPlace = ""

Me.TxtSurname.SetFocus
With FrmNewClient
.Move 400, 10

End With

End Sub


So my question is, where to put the new program lines? Till now, i get invalid

 

[Andrew Poulsom]

You put the code in the UserForm, which must have a CommandButton named CommandButton1. To test it enter something in the TextBox and press the CommandButton.

 

[wimven]

Thank you guys. Here's what it became with your help.
If the postcode is valid must not be determined by a button or in the end but whenever you push enter to go forward to the next field.

Private Sub TxtPostcd_AfterUpdate()

Pc = TxtPostcd.Text
TxtPostcd = Left(Pc, 4) & " " & UCase(Right(Pc, 2))
If Not TxtPostcd.Text Like "[0-9][0-9][0-9][0-9] [A-Z][A-Z]" Then
MsgBox "Invalid code, please retry"
End If
End Sub

 

[wimven]

Hi guys,

Here's what it became finally. Take notice of the bOk lines thanks to a Dutch helper. If the code is OK it tells the cursor to move on, if not the cursor remains in the field.

Option Explicit
Public bOk As Boolean

Private Sub TxtPostcd_AfterUpdate()
Dim pc As String
bOk = True
If TxtPostcd.Text = "" Then Exit Sub
pc = UCase(Replace(TxtPostcd.Text, " ", ""))
If Not pc Like "####[A-Z][A-Z]" Or Len(pc) <> 6 Then 'check op lengte en op opmaak
bOk = False
MsgBox "Ongeldige invoer" & vbCrLf & "" & vbCrLf & "Probeer opnieuw", vbOKOnly


TxtPostcd.Text = ""
Exit Sub
End If
pc = Left(pc, 4) & " " & Right(pc, 2)
If pc <> TxtPostcd.Text Then TxtPostcd.Text = pc
End Sub

Private Sub txtPostcd_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not bOk Then Cancel = True
End Sub

Private Sub userform_click()
End Sub