Case sensative list validation
 

Is there a way to validate against a list and return invalid if it does not
match the case of the list. Here is the code i'm using now in my macro:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$IF$5:$IF$20000"
.InCellDropdown = False
End With

One of the cells in the list contains "Hardwood Lumber - BF," however it
says that "hardwood lumber - bf" is valid. Any ideas?

Case sensative list validation
 

You could try this

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=EXACT(" & ActiveCell.Address(False, False) &
_
",INDEX($I$5:$I$20000,MATCH(" & ActiveCell.Address(False, False)
& _
",$I$5:$I$20000,0)))"
.InCellDropdown = False
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave L" wrote in message
...
Is there a way to validate against a list and return invalid if it does
not
match the case of the list. Here is the code i'm using now in my macro:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$IF$5:$IF$20000"
.InCellDropdown = False
End With

One of the cells in the list contains "Hardwood Lumber - BF," however it
says that "hardwood lumber - bf" is valid. Any ideas?

Case sensative list validation
 

Thanks Bob, but i got a compile syntax error when i tried to run it. I'm kind
of a novice with VBA so I don't know how to fix it.

"Bob Phillips" wrote:

You could try this

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=EXACT(" & ActiveCell.Address(False, False) &
_
",INDEX($I$5:$I$20000,MATCH(" & ActiveCell.Address(False, False)
& _
",$I$5:$I$20000,0)))"
.InCellDropdown = False
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave L" wrote in message
...
Is there a way to validate against a list and return invalid if it does
not
match the case of the list. Here is the code i'm using now in my macro:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="=$IF$5:$IF$20000"
.InCellDropdown = False
End With

One of the cells in the list contains "Hardwood Lumber - BF," however it
says that "hardwood lumber - bf" is valid. Any ideas?

Case sensative list validation
 

maybe wrap-around. Try this version

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, _
AlertStyle:=xlValidAlertStop, _
Operator:= xlBetween, _
Formula1:="=EXACT(" & ActiveCell.Address(False, False) _
& ",INDEX($I$5:$I$20000,MATCH(" _
& ActiveCell.Address(False, False) _
& _",$I$5:$I$20000,0)))"
.InCellDropdown
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Dave L" wrote in message
...
Thanks Bob, but i got a compile syntax error when i tried to run it. I'm
kind
of a novice with VBA so I don't know how to fix it.

"Bob Phillips" wrote:

You could try this

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=EXACT(" & ActiveCell.Address(False, False)
&
_
",INDEX($I$5:$I$20000,MATCH(" & ActiveCell.Address(False,
False)
& _
",$I$5:$I$20000,0)))"
.InCellDropdown = False
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Dave L" wrote in message
...
Is there a way to validate against a list and return invalid if it does
not
match the case of the list. Here is the code i'm using now in my macro:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="=$IF$5:$IF$20000"
.InCellDropdown = False
End With

One of the cells in the list contains "Hardwood Lumber - BF," however
it
says that "hardwood lumber - bf" is valid. Any ideas?