Excel中的命名区分大小写验证 [英] Case sensitive validation in Excel from Named List

问题描述

我正在Excel 2003中使用VBA来应用验证，以将验证应用于命名列表中给定范围的单元格.然后，用户可以从值的下拉列表中进行选择.

给定名为"MyLookupList"的命名范围，这就是我设置验证的方式

 具有validatedRange.Validation.删除.添加类型:= xlValidateList，AlertStyle:= xlValidAlertStop，_运算子:= xlBetween，Formula1:="= MyLookupList".ErrorMessage =无效值.从下拉列表中选择一个..InCellDropdown =真结束于 

一切正常，但是问题是从命名列表应用验证时，它不区分大小写.IE.如果下拉选项为"John Smith"，则用户可以在已验证的单元格中键入"john smith"或"john SmiTh"，Excel仍会将其视为有效条目.

我知道通过Tools-> Validation ...手动创建列表将使查找验证区分大小写，但就我而言，这是不可行的-我必须填充命名列表并以编程方式分配验证./p>

有人知道确保基于命名列表的Excel验证区分大小写的方法吗?

谢谢.

解决方案

看看这个:

http://www.contextures.com/xlDataVal14.html

我还没有测试它，但是它有点复杂，但是我认为它可以满足您的要求.

I'm using VBA in Excel 2003 to apply validation to apply validation to a given range of cells from a named list. The user can then select from a dropdown list of values.

Edit: Here's how I'm setting the validation, given a named range called 'MyLookupList'

        With validatedRange.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:="=MyLookupList"
            .ErrorMessage = "Invalid value. Select one from the dropdown list."
            .InCellDropdown = True
        End With

All that works fine, but the problem is that when validation is applied from a named list, it is case-insensitive. I.e. if a dropdown choice is "John Smith", then the user can type in "john smith" or "john SmiTh" into the validated cell and Excel will still treat it as a valid entry.

I know that manually creating a list via Tools-->Validation... will make the lookup validation case sensitive, but for my case this is just not feasible - I have to populate the named lists and assign validation programmatically.

Does anyone know of a way to ensure that Excel validation based on named lists is case-sensitive?

Thanks.

解决方案

Have a look at this:

http://www.contextures.com/xlDataVal14.html

I haven't tested it and it's a bit more complicated but I think it will do what you want.

这篇关于Excel中的命名区分大小写验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！