I have been using the code below which is a very slight adaptation of Myrna's original recommendation. As Jamie, kindly pointed out there are 8 exceptions to the UK Postcode system which start with just one alpha character! I need to be able to let these pass through, so basically I need to allow both the following codes...

"[A-Z][A-Z]## #[A-Z][A-Z]"
and
"[A-Z]## #[A-Z][A-Z]"

The 8 Codes that break the rule a begin with the following...

B - Birmingham
E - East London
G - Glasgow
L - Liverpool
M - Manchester
N - North London
S - Sheffield
W - West London

Hope this makes sense? Maybe once they have put the postcode in maybe a dialog box can confirm that the address is indeed "Manchester?" for example...


One more point I need addressing is that the first part of the postcode is sometimes a single figure. I will need for the code to put in a 0(zero) to cover this... For example their post code maybe SW4 2AP I need the program to realise that in order to format correctly it needs to add a 0 i.e. the correct results would be SW04 2AP. This is due to the users only putting it in lazily and not fully understanding the postal coding system.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As String

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("F2:F25000")) Is Nothing Then
With Target
X = UCase$(.Value)
If X Like "[A-Z][A-Z]0# #[A-Z][A-Z]" Then
'it's OK as is
ElseIf X Like "[A-Z][A-Z]0##[A-Z][A-Z]" Then
X = Left$(X, 4) & " " & Right$(X, 3)
Else
MsgBox "Incorrect format: AA0# #AA", vbOKOnly, "Error!"
'leave the string as-is so they can correct without
'retyping the whole thing
End If
.Value = X
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Hope you guys or somebody else can help me on this one. I'm more used to using vb.net and VBA is turning me around and I don't want to conflict my thoughts.

Scott