Validating data

[Guest]

I want to make sure users enter a UK post code in a valid format. I can get Excel to accep
=AND(LEN(A1)=6,ISNUMBER(VALUE(MID($A1,1,1)))=FALSE,ISNUMBER(VALUE(MID($A1,2,1)))=TRUE,ISNUMBER(VALUE(MID($A1,3,1)))=FALSE,ISNUMBER(VALUE(MID($A1,4,1)))=TRUE,ISNUMBER(VALUE(MID($A1,5,1)))=FALSE,ISNUMBER(VALUE(MID($A1,6,1)))=FALSE

for AN NAA where A=letter and N=number

However, that's just one of the accepted formats for post codes, how do I get Excel to do an OR. What I'm trying to say is, how do I do thi
(postCode Like "[a-z][a-z][0-9][0-9]*[0-9][a-z][a-z]") Or (postCode Like "[a-z][0-9]*[0-9][a-z][a-z]") Or (postCode Like "[a-z][0-9][0-9]*[0-9][a-z][a-z]") Or (postCode Like "[a-z][a-z][0-9]*[0-9][a-z][a-z]") Or (postCode Like "[a-z][0-9][a-z]*[0-9][a-z][a-z]") Or (postCode Like "[a-z][a-z][0-9][a-z]*[0-9][a-z][a-z]") Or (postCode Like "[a-z][a-z][0-9][0-9]*[a-z][a-z][a-z]"
which works in VBA, in the data validation formula

Thanks.

 

[Bernie Deitrick]

Daniel,

Instead of using the built in Data Validation, you can roll your own using
VBA and the worksheet change event. Copy the code below, right click on the
sheet tab, select "View Code", and paste the code in the window that
appears. The code is written to validate entries in A1:A10, but can be
easily changed (as can the messages).

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PostCode As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("A1:A10"), Target) Is Nothing Then
Application.EnableEvents = False
PostCode = Target.Value
If (PostCode Like "[a-z][a-z][0-9][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9][a-z]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9][a-z]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9][0-9]*[a-z][a-z][a-z]") Then
MsgBox "That was a good postcode, Daniel."
Else
Application.Undo
MsgBox "That was an invalid entry in cell " _
& Target.Address(False, False) & ", Daniel."
End If
Application.EnableEvents = True
End If
End Sub

I want to make sure users enter a UK post code in a valid format. I can get Excel to accept
=AND(LEN(A1)=6,ISNUMBER(VALUE(MID($A1,1,1)))=FALSE,ISNUMBER(VALUE(MID($A1,2,
1)))=TRUE,ISNUMBER(VALUE(MID($A1,3,1)))=FALSE,ISNUMBER(VALUE(MID($A1,4,1)))=
TRUE,ISNUMBER(VALUE(MID($A1,5,1)))=FALSE,ISNUMBER(VALUE(MID($A1,6,1)))=FALSE
)

for AN NAA where A=letter and N=number.

However, that's just one of the accepted formats for post codes, how do I

get Excel to do an OR. What I'm trying to say is, how do I do this

(postCode Like "[a-z][a-z][0-9][0-9]*[0-9][a-z][a-z]") Or (postCode Like

"[a-z][0-9]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][0-9][0-9]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][a-z][0-9]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][0-9][a-z]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][a-z][0-9][a-z]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][a-z][0-9][0-9]*[a-z][a-z][a-z]")

 

[Dave Peterson]

And stealing Bernie's code, you could modify it so that it will capitalize your
letters and not care if you enter the value in upper or lower case:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim PostCode As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("A1:A10"), Target) Is Nothing Then
Application.EnableEvents = False
PostCode = LCase(Target.Value)
If (PostCode Like "[a-z][a-z][0-9][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9][a-z]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9][a-z]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9][0-9]*[a-z][a-z][a-z]") Then
Target.Value = UCase(PostCode)
MsgBox "That was a good postcode, Daniel."
Else
Application.Undo
MsgBox "That was an invalid entry in cell " _
& Target.Address(False, False) & ", Daniel."
End If
Application.EnableEvents = True
End If
End Sub

Daniel,

Instead of using the built in Data Validation, you can roll your own using
VBA and the worksheet change event. Copy the code below, right click on the
sheet tab, select "View Code", and paste the code in the window that
appears. The code is written to validate entries in A1:A10, but can be
easily changed (as can the messages).

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PostCode As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("A1:A10"), Target) Is Nothing Then
Application.EnableEvents = False
PostCode = Target.Value
If (PostCode Like "[a-z][a-z][0-9][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][0-9][a-z]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9][a-z]*[0-9][a-z][a-z]") _
Or (PostCode Like "[a-z][a-z][0-9][0-9]*[a-z][a-z][a-z]") Then
MsgBox "That was a good postcode, Daniel."
Else
Application.Undo
MsgBox "That was an invalid entry in cell " _
& Target.Address(False, False) & ", Daniel."
End If
Application.EnableEvents = True
End If
End Sub

I want to make sure users enter a UK post code in a valid format. I can get Excel to accept
=AND(LEN(A1)=6,ISNUMBER(VALUE(MID($A1,1,1)))=FALSE,ISNUMBER(VALUE(MID($A1,2,
1)))=TRUE,ISNUMBER(VALUE(MID($A1,3,1)))=FALSE,ISNUMBER(VALUE(MID($A1,4,1)))=
TRUE,ISNUMBER(VALUE(MID($A1,5,1)))=FALSE,ISNUMBER(VALUE(MID($A1,6,1)))=FALSE
)

for AN NAA where A=letter and N=number.

However, that's just one of the accepted formats for post codes, how do I

get Excel to do an OR. What I'm trying to say is, how do I do this

(postCode Like "[a-z][a-z][0-9][0-9]*[0-9][a-z][a-z]") Or (postCode Like

"[a-z][0-9]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][0-9][0-9]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][a-z][0-9]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][0-9][a-z]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][a-z][0-9][a-z]*[0-9][a-z][a-z]") Or (postCode Like
"[a-z][a-z][0-9][0-9]*[a-z][a-z][a-z]")

which works in VBA, in the data validation formula?

Thanks.

 

[Guest]

Thanks!

 

[Guest]

Just a quick question, would you mind if I borrowed the code? Don't want to get into any copyright problems. Thanks.

 

[Bernie Deitrick]

Daniel,

Dave was just using hyperbole, when he said he was 'stealing' my code: he
simply meant that he was making a small tweak to my code and wanted to
acknowledge that I had done most of the 'work' on developing it.

You needn't ask permission - all posts to newsgroups are in the public
domain, put there in hopes that they will be used, now and in the future
when others google the groups.

Good luck,

HTH,
Bernie
MS Excel MVP

Just a quick question, would you mind if I borrowed the code? Don't want

to get into any copyright problems. Thanks.

 

[Dave Peterson]

And just to add to what Bernie wrote:

When you paste the code into your macro, put a date that you stole, er, pasted
the code.

If you get a chance, search google and find the link to the thread. Here it is:

http://groups.google.com/[email protected]

Paste all those things in as comments.

Someday, you're going to be tweaking the only copy of the code and you're going
to destroy it. (Am I the only one?) This'll save a few minutes when you're
trying to get it back to working.

(I wish I had done this when I was "acknowledging" other people's code in my
programs!)

 

[Guest]

Isn't there a simple method of formatting a cell in Excel to show the entered data in the UK Post Code format? Which is what I'm after, really. Any help would be appreciated.

 

[Debra Dalgleish]

Excel only has built-in formats for numbers. The UK Post Code is a
combination of letters and numbers, so formatting it is more difficult.
You can use an event macro, as described by David McRitchie in the
following thread:


http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=u9EiKHVnBHA.1600@tkmsftngp07