Setting validation with VBA

[Les Isaacs]

Hi All
I have a routine that generates a complex excel file (the routine runs from an access application) using vba. A lot of validation is set - e.g.

Please Login or Register  to view this content.

I would like to amend the particular validation above so that only the characters Y or N are allowed - while still retaining the required text length of 7. Can this be done? I realise I could do this with some 'change event' code, but would much prefer to prevent invalid characters rather than just give a warning about them.
Hope someone can help.
Thanks in advance.
Les

[JosephP]

you would need to use a custom validation with a formula such as
=and(len(C1)=7,LEN(SUBSTITUTE(SUBSTITUTE(UPPER(C1),"Y",""),"N",""))=0)

because this requires relative addressing you have to select each cell as you apply the validation, or apply the validation to an entire column range at a time and select the first cell before applying the validation

[Les Isaacs]

Hi JosephP
Many thanks for your reply.
I tried your formula in a blank workbook and it works exactly as required (as you knew it would ), but I'm having problems trying to use it in the vba. I have:

Please Login or Register  to view this content.

but this generates an error 1004 - Application defined or object defined error.
The above works fine for lots of simpler validation that I have set, so I'm guessing that the problem is to do with the relative addressing
Any ideas?
Thanks again for your help.
Les

[JosephP]

I reckon that oughta be

Please Login or Register  to view this content.

[Les Isaacs]

Hi JosephP
Genius
Getting those quotes right has always been a nightmare for me, so very many thanks.
Les

[JosephP]

you're welcome :-)

please don't forget to mark the thread solved (click the 'thread tools' link at the top, then 'mark solved')