Hi
I'm trying to apply data validation to a cell that is in line with common
postcode structures. I thought there was a way of dictating an entry had to
be a letter, could be a letter or number but was compulsory, could be letter
or number or blank.

Or maybe this was an Access feature?
Any help gratefully recieved
Thanks

Hi,

I assume you mean UK postcodes which are in the format CH63 3HZ note the
compulsory space for a valid postcode. Right click your sheet tab, view code
and past the code below in. Change the range to the range you are trying to
validate, currently it is set for column A. If you enter an invalid code then
the cell will be cleared with a message

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, Outstring As String
Dim RegExp As Object, Collection As Object, RegMatch As Object
Set MyRange = Range("A:A") 'Change to suit
If Intersect(Target, MyRange) Is Nothing Or _
Target.Cells.Count 1 Then Exit Sub
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = False
.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})"
End With
Set MyRange = Target
Outstring = ""
Set Collection = RegExp.Execute(Target)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
If Target < "" And Target.Value < Outstring Then
MsgBox "Invalid UK Postcode"
Application.EnableEvents = False
With Target
.Select
.ClearContents
End With
Application.EnableEvents = True
End If
Set Collection = Nothing
Set RegExp = Nothing
Set MyRange = Nothing
End Sub

Mike

"JohnG" wrote:

Hi
I'm trying to apply data validation to a cell that is in line with common
postcode structures. I thought there was a way of dictating an entry had to
be a letter, could be a letter or number but was compulsory, could be letter
or number or blank.

Or maybe this was an Access feature?
Any help gratefully recieved
Thanks

Thanks Mike - that sorted it.

John

"Mike H" wrote:

Hi,

I assume you mean UK postcodes which are in the format CH63 3HZ note the
compulsory space for a valid postcode. Right click your sheet tab, view code
and past the code below in. Change the range to the range you are trying to
validate, currently it is set for column A. If you enter an invalid code then
the cell will be cleared with a message

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, Outstring As String
Dim RegExp As Object, Collection As Object, RegMatch As Object
Set MyRange = Range("A:A") 'Change to suit
If Intersect(Target, MyRange) Is Nothing Or _
Target.Cells.Count 1 Then Exit Sub
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = False
.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})"
End With
Set MyRange = Target
Outstring = ""
Set Collection = RegExp.Execute(Target)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
If Target < "" And Target.Value < Outstring Then
MsgBox "Invalid UK Postcode"
Application.EnableEvents = False
With Target
.Select
.ClearContents
End With
Application.EnableEvents = True
End If
Set Collection = Nothing
Set RegExp = Nothing
Set MyRange = Nothing
End Sub

Mike

"JohnG" wrote:

Hi
I'm trying to apply data validation to a cell that is in line with common
postcode structures. I thought there was a way of dictating an entry had to
be a letter, could be a letter or number but was compulsory, could be letter
or number or blank.

Or maybe this was an Access feature?
Any help gratefully recieved
Thanks

Works wonderful
Ken

"Mike H" wrote:

Hi,

I assume you mean UK postcodes which are in the format CH63 3HZ note the
compulsory space for a valid postcode. Right click your sheet tab, view code
and past the code below in. Change the range to the range you are trying to
validate, currently it is set for column A. If you enter an invalid code then
the cell will be cleared with a message

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range, Outstring As String
Dim RegExp As Object, Collection As Object, RegMatch As Object
Set MyRange = Range("A:A") 'Change to suit
If Intersect(Target, MyRange) Is Nothing Or _
Target.Cells.Count 1 Then Exit Sub
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = False
.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
& "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
& "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
& "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
& "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
& "\d(?:\d|[A-Z])? \d[A-Z]{2})"
End With
Set MyRange = Target
Outstring = ""
Set Collection = RegExp.Execute(Target)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
If Target < "" And Target.Value < Outstring Then
MsgBox "Invalid UK Postcode"
Application.EnableEvents = False
With Target
.Select
.ClearContents
End With
Application.EnableEvents = True
End If
Set Collection = Nothing
Set RegExp = Nothing
Set MyRange = Nothing
End Sub

Mike

"JohnG" wrote:

Hi
I'm trying to apply data validation to a cell that is in line with common
postcode structures. I thought there was a way of dictating an entry had to
be a letter, could be a letter or number but was compulsory, could be letter
or number or blank.

Or maybe this was an Access feature?
Any help gratefully recieved
Thanks