On Mon, 27 Jul 2009 22:38:36 +0100, Patrick Molloy wrote:

it works in 2003, otherwise I wouldn't have posted it.
the x1 coerces the text to a number


see my demo book:
http://cid-b8e56c9a5f311cb7.skydrive...validation.xls


"salgud" wrote in message
...
On Mon, 27 Jul 2009 17:17:39 +0100, Patrick Molloy wrote:

the custom validation formula is (for cell C6 )

=AND( LEN(C6)=7, ISNUMBER( MID(C6,2,6) *1 ) )

this checks (1) that the entry is 7 characters and (2) that the 2nd thro
last are numeric
-- if any if the last 6 are not numbers then the last 6 cannot be numeric



"salgud" wrote in message
. ..
On Fri, 24 Jul 2009 22:11:59 +0100, Patrick Molloy wrote:

here is a function that you can use

Function ValidEntry(text As String) As Boolean
'input 7 characters chr 1 is A:Z 2-7 are numeric
If Len(text) < 7 Then Exit Function
Select Case Left(text, 1)
Case "a" To "z", "A" To "Z"
Case Else
Exit Function
End Select
If IsNumeric(Mid(text, 2)) Then
ValidEntry = True
End If
End Function

first check is for 7 characters
second check is the first character is a-z or A-Z
last check is that the last 6 characters "isnumeric" which must be true
only
if all of the characters are numbers



"salgud" wrote in message
.. .
On Fri, 24 Jul 2009 11:33:27 -0600, salgud wrote:

I've submitted this before, but still can't get it to run. I'm
entering
validation criteria into a spreadsheet, but keep getting an
"Application
or
object not defined" error on the .Add Type line. The validation
formula
is
looking for a 7 digit number, the first of which must be an alpha
character, the other 6 must be numbers.

Public Sub DataValidationClientID()
'Validate that Client ID field has a correct entry before allowing
entry
of
Client Last Name
With Selection.Validation
.Delete

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="=AND(CODE(UPPER(B" & lCurRow &
"))64,CODE(UPPER(B" & _
lCurRow & "))<91,LEN(B" & lCurRow & ")=7,ISNUMBER(VALUE(RIGHT(B"
&
lCurRow & ",6)))" <----------- ERROR
' .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:=
_
' xlBetween,
Formula1:="=AND(CODE(UPPER(B7))64,CODE(UPPER(B7)) <91,LEN(B7)=7,ISNUMBER(VALUE(RIGHT(B7,6))))"


.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Incorrect ClientID"
.InputMessage = ""
.ErrorMessage = "There is no Client ID or an incorrect Client ID. "
_
& "Please enter a correct Client ID in Column B before entering a
Client
Name"
.ShowInput = False
.ShowError = True
End With

Does anyone see the problem? Thanks in advance.

I should have mentioned, the variable lCurRow is declared and has the
correct value when the program halts, and the correct cells for
validation
are selected.

Thanks for your reply. I know I can check it using code. In fact, the
final
phase of this program will be to run all these same checks on the
spreadsheet in case they found a way around the validation (order of
entry
effects validation). But for the original data entry person, I want to
use
data validation so they know when the enter the data whether they're
entry
is correct or not.

This seems far more difficult than I expected. Does anyone else have any
ideas why this code doesn't work?

Thanks for your reply. That formula doesn't work, not sure why. But I
don't
understand why you're multiplying by 1. Either way, when I test it, I get
true when the first character is a number (7 numbers, no first letter).

Thanks for your reply, Patrick. I too amd using XL2003, which helps.

Acutally, the second spreadsheet demos that that forumula doesn't work.
Note that the first example, A123456, does meet the criteria I'm looking
for (one letter, six numbers) and your validation formula yields "FALSE".
That one needs to be TRUE.

I thought maybe the multiplying by 1 did something like that. Clever.

In any case, I'm giving up on validating the ID number. I've gotten several
formulae that do work in the spreadsheet, but none of them translate into
VBA for creating data validation criteria. I've spend the better part of
several days trying to get this to work, with no sucess. I had always
planned to have a separate macro to check all these validated data and to
mark any that don't meet the criteria. With this one, I'll just do a VBA
check on the ID field and if the ID fails, send the spreadsheet back to the
user to correct. Not as efficient as I wanted to be, but more efficient
than spending more fruitless days trying to get data validation to work on
this. I guess there are limits to what can be done by any method, and I've
hit the limits for this one. I need to move ahead and get some work done.

Nonetheless, I do appreciate everyone's efforts at helping me make this
work. Thank you all for your suggestions!