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.

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.

I can't give you the technical explanation for why this works, but hopefully
this will help you keep moving forward, and one of the real gurus here can
provide the "why".

Although the formula can be entered directly, and a recorded macro also
seems to continue to work, as soon as that formula string is broken out into
a new line, or a concatenation of statements, it seems to fail for me too. I
kind of suspect it has something to do with your use of the AND statement,
and lumping all of this in Formula1 (vs Formula2) but that's the technical
part I don't understand.

There are probably even more eloquent ways to recreate your formula, I just
used one that came to mind, and it seems to be working. Please give it a try,
and if it doesn't work when tested against your real worksheet post back to
the group.

Range("A1").Select
With Selection.Validation
.Delete
lCurRow = 6
StrTest = "=SUMPRODUCT((CODE(UPPER(B" & lCurRow &
"))64)*1,(CODE(UPPER(B" & lCurRow & "))<91)*1,(LEN(B" & lCurRow &
")=7)*1,(ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6))))*1)"
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=StrTest
End With

HTH,
Keith

"salgud" wrote:

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.

On Fri, 24 Jul 2009 12:44:01 -0700, ker_01 wrote:

I can't give you the technical explanation for why this works, but hopefully
this will help you keep moving forward, and one of the real gurus here can
provide the "why".

Although the formula can be entered directly, and a recorded macro also
seems to continue to work, as soon as that formula string is broken out into
a new line, or a concatenation of statements, it seems to fail for me too. I
kind of suspect it has something to do with your use of the AND statement,
and lumping all of this in Formula1 (vs Formula2) but that's the technical
part I don't understand.

There are probably even more eloquent ways to recreate your formula, I just
used one that came to mind, and it seems to be working. Please give it a try,
and if it doesn't work when tested against your real worksheet post back to
the group.

Range("A1").Select
With Selection.Validation
.Delete
lCurRow = 6
StrTest = "=SUMPRODUCT((CODE(UPPER(B" & lCurRow &
"))64)*1,(CODE(UPPER(B" & lCurRow & "))<91)*1,(LEN(B" & lCurRow &
")=7)*1,(ISNUMBER(VALUE(RIGHT(B" & lCurRow & ",6))))*1)"
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=StrTest
End With

HTH,
Keith

"salgud" wrote:

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. Gave your code a try, same error.

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.

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?

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?

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).

For starters, I don't see a value assigned to lCurRow before you try to use
it...

HTH
Keith

"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.

On Fri, 24 Jul 2009 10:48:01 -0700, ker_01 wrote:

For starters, I don't see a value assigned to lCurRow before you try to use
it...

HTH
Keith

"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.


See my follow up post below