View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Patrick Molloy Patrick Molloy is offline
external usenet poster
 
Posts: 1,049
Default Still not flying

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.