Yet more validation ... challenges!

[Les Isaacs]

Bob

The NI number is validated using data validation, not code. The DV formula
is
=AND(LEN(L14)=9,ISNUMBER(MATCH(LEFT(L14,2),VALID_FIRST,0)),ISNUMBER(MATCH(RIGHT(L14,1),VALID_LAST,0)),ISNUMBER(--MID(L14,3,5)))
Valid_first and valid_last are lists in sheet2.

As you say, hitting delete on a valid postcode does generate the error
message, which it shouldn't, but at least it then restores the valid
postcode. Also, curiously, this does not cause the latest problem where
hitting the delete key seems to stop the rest of the validation code from
working.

To get the error, I just have to open the workbook and either immediately or
at any time I hit the delete key while the active cell is anywhere EXCEPT IN
THE NI NUMBER COLUMN (column L)!!!!

To be honest, coming to it this morning I really wondered whether I had
dreamt it all: unfortunately not! I really thought I was getting somewhere
with my understanding of excel coding, but this completely defeats me. Hope
you can see what's going on: or doesn't it happen south of Watford?

Thanks again
Les

 

[Bob Phillips]

Bob

The NI number is validated using data validation, not code. The DV formula
is
=AND(LEN(L14)=9,ISNUMBER(MATCH(LEFT(L14,2),VALID_FIRST,0)),ISNUMBER(MATCH(RIGHT(L14,1),VALID_LAST,0)),ISNUMBER(--MID(L14,3,5)))
Valid_first and valid_last are lists in sheet2.

So really, this is incidental to the problem?

As you say, hitting delete on a valid postcode does generate the error
message, which it shouldn't, but at least it then restores the valid
postcode. Also, curiously, this does not cause the latest problem where
hitting the delete key seems to stop the rest of the validation code from
working.

Do you want it that the code will allow a delete to clear out a cell and
remove the postcode without an error?

To get the error, I just have to open the workbook and either immediately
or at any time I hit the delete key while the active cell is anywhere
EXCEPT IN THE NI NUMBER COLUMN (column L)!!!!

I didn't get that error in my test. I will re-try it.

To be honest, coming to it this morning I really wondered whether I had
dreamt it all: unfortunately not! I really thought I was getting somewhere
with my understanding of excel coding, but this completely defeats me.
Hope you can see what's going on: or doesn't it happen south of Watford?

Whilst I will test it again, it doesn't happen down here.

Also the sun always shines down here; the women are all beautiful; and, all
the kids are well behaved. They call it the north-south divide.

 

[Les Isaacs]

Bob

So really, this is incidental to the problem?

Yes (sorry!).

Do you want it that the code will allow a delete to clear out a cell and
remove the postcode without an error?

Yes (please!)

I didn't get that error in my test. I will re-try it.

It is entirely consistent here.

Whilst I will test it again, it doesn't happen down here.

Shall I email you my workbook?

Also the sun always shines down here; the women are all beautiful; and,
all the kids are well behaved. They call it the north-south divide.

I'm on my way!

Les

 

[Bob Phillips]

Les,

Send it over, that will cut to the quick

bob dot phillips at tiscali dot co dot uk

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Not received anything yet Les.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Leslie Isaacs]

Bob

That's frustrating: in fact I checked last night and saw that what I had
sent yesterday moring was to the wrong address (one 'l' in phillips), so I
sent it again with the right address - at around 11.30pm. I'm in the other
office now, but will check again and resend this evening.

Thanks for bearing with me.
Les

 

[Bob Phillips]

It's okay, the second got caught in the spam filter. I have it now.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

.... but I cannot reproduce the problem Les.

I enter an invalid postcode - it errors. I enter another invalid code, it
errors. If I select something else and come back, it still errors.

This is very odd.