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