National Insurance Validation

**Sirishgreen** · 03-16-2009, 06:51 AM

Hi

I have this code for validating a national insurance number but how do i assign this to a cell in excel.

Please Login or Register  to view this content.

Thanks for the help
Simon

**DonkeyOte** · 03-16-2009, 07:06 AM

You may want to think about applying via a Worksheet_Change event, eg:

Please Login or Register  to view this content.

Right Click on Tab to which the validation is to be applied and select View Code, paste the above into resulting window - change the Range (in red) to be that to which the Validation is to be applied.

You could if you wanted to shorten the Function via Regular Expressions... but if you're comfortable with what you have you should keep it.

I'm not aware of a way in which a custom UDF can be called directly from within Custom Data Validation formula... you may be able to... I can't answer definitively.

**DonkeyOte** · 03-16-2009, 07:15 AM

I should add that you could of course revert to a standard Custom Validation Formula approach, eg if B3 is first cell of interest then highlight B3 and set up Validation -> Custom: Formula:

=AND(AND(CODE(LEFT(B3,1))>=65,CODE(LEFT(B3,1))<=90),AND(CODE(MID(B3,2,1))>=65,CODE(MID(B3,2,1))<=90),AND(CODE(RIGHT(B3,1))>=65,CODE(RIGHT(B3,1))<=90),ISNUMBER(0+MID(B3,3,6)))

Then you can dispense with UDF / VBA altogether.

**Sirishgreen** · 03-18-2009, 08:03 AM

Thanks for the response.

I like the idea of using the custom validation over vb, but the formula you left evaluates to an error.

If this is not simple to amend then i will go with other method,

Thanks again

Simon

**DonkeyOte** · 03-18-2009, 08:08 AM

...the formula you left evaluates to an error

Presumably because when you're applying it the cell does not contain an NI number...........just click Yes on prompt.

**Sirishgreen** · 03-19-2009, 10:08 AM

when i apply The validation i get the popup saying currently evaluates to an error. So i carried on as normal but I am able to enter any value into the cell it's not validating as it should.