Still not flying

[salgud]

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.

 

[ker_01]

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

HTH
Keith

 

[salgud]

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.

 

[salgud]

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

HTH
Keith

See my follow up post below

 

[ker_01]

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

 

[Patrick Molloy]

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]

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

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?

 

[salgud]

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

Thanks for your reply. Gave your code a try, same error.

 

[Patrick Molloy]

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]

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

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

 

[ker_01]

Patrick's sample appears to cover two of your three criteria. I just tested
this, and it works on Excel2003 on my PC (on cell A1) and incorporates your
need for a first-digit alpha between a-z or A-Z.

.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween,
Formula1:="=AND(LEN(A1)=7,ISNUMBER(MID(A1,2,6)*1),CODE(LEFT(UPPER(A1),1))>64,CODE(LEFT(UPPER(A1),1))<91)"

However, I'm still not clear that this will solve your original request-
your original validation formula was good, the problem was getting VBA to
assign a text string or multi-part string (using lCurrRow) to a validation
formula.

Let me propose a workaround; use the formula above to create the validation
in an area of the worksheet you aren't using- maybe cell GG1. Then use
copy/paste to copy the validation formula to your target cells. When you are
done you can always delete GG1, if you feel that is necessary. Copy the
validation without retaining any existing entered value using something like:

Range("GG1").Select
Selection.Copy
Range("F" & lCurrRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.ClearContents 'to get rid of any values that were in the
source cell

(note: this is longhand- you can collapse this into fewer lines of code if
you want)

You asked about the *1; it looks like the multiplication by 1 is a shorthand
way to evaluate a string number back into a "real" number without having to
use the VALUE function. New to me, I'll have to remember that.

I still don't know why your original attempt didn't work, but hopefully the
above workaround will get you back on track

HTH,
Keith

 

[Patrick Molloy]

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.skydriv...ic/Excel Files/Data Validation/validation.xls

 

[ker_01]

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

I definitely wasn't doubting you, nor did I intend to convey any disrespect-
I've been using XL (still using 2003 whenever possible) for a long time, and
it is little gems like this that remind me that my code is still plenty
inefficient ;-)

I continually appreciate your postings, along with the other MVPs and kind
experts who contribute to the group.

Best,
Keith

 

[salgud]

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.skydriv...ic/Excel Files/Data Validation/validation.xls

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!