Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do formulas have to be case sensative when searching words | Excel Discussion (Misc queries) | |||
case sensetive data validation | Excel Worksheet Functions | |||
Validation vs Case | Excel Discussion (Misc queries) | |||
Validation ... Case Sensitive? | Excel Discussion (Misc queries) | |||
Case Sensitivity problem with data validation | Excel Worksheet Functions |