Excel vba：强制数据验证和最大字段长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4486885/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel vba: data validation mandatory and maximum field length
提问by Mohamed Saligh
I want to have some data validations for the given range ( D2:D65536 ).
我想对给定范围 ( D2:D65536 ) 进行一些数据验证。
The validation criteria must check the cells within the range must not be empty and the text length should not more than 35.
验证标准必须检查范围内的单元格不能为空，文本长度不能超过 35。
How to implement this validation using vba?
如何使用 vba 实现此验证？
I have tried:
我试过了：
' With Range("D2:D65536").Validation
' .Delete
' .Add Type:=xlValidateTextLength, _
' AlertStyle:=xlValidAlertInformation, _
' Minimum:=2, Maximum:="35"
' .IgnoreBlank = True
' .ErrorTitle = "Validation Error"
' .ErrorMessage = "X mandatory and length should be less than or equal to 35"
' .ShowError = True
' End With
回答by Fionnuala
I think you are looking for:
我认为您正在寻找：
Sub Validate()
With Range("D2:D65536").Validation
.Delete
.Add Type:=xlValidateTextLength, _
AlertStyle:=xlValidAlertInformation, _
Operator:=xlBetween, _
Formula1:="2", _
Formula2:="35"
.IgnoreBlank = True
.ErrorTitle = "Validation Error"
.ErrorMessage = "X mandatory and length should be less than or equal to 35"
.ShowError = True
End With
End Sub
The Help on F1 is less confusing than on a search, but any way have a look at the Add method : http://msdn.microsoft.com/en-us/library/aa221688(v=office.11).aspx. The example you used is for whole numbers, but you wish to check text length.
F1 上的帮助不像搜索那样令人困惑，但无论如何都可以查看添加方法：http: //msdn.microsoft.com/en-us/library/aa221688(v=office.11).aspx。您使用的示例适用于整数，但您希望检查文本长度。
回答by Dr. belisarius
Function checkrange()
Dim r, a As Range
'Set r = Range("D2:D65536")
Set r = Range("a1:a2")
checkrange = False
For Each a In r
If (a = "" Or Len(a) > 35) Then Exit Function
Next a
checkrange = True
End Function
Sub a()
MsgBox (checkrange())
End Sub
Return True if validation succeeds, False otherwise.
如果验证成功，则返回 True，否则返回 False。
HTH!
哼！
Edit
编辑
You can read an approach done by using Validation here. But I think it is not robust enough for production code. Validating empty cells without VBA is not possible in a consistent way AFAIK.
您可以在此处阅读使用验证完成的方法 。但我认为它对于生产代码来说不够健壮。AFAIK 无法以一致的方式验证没有 VBA 的空单元格。
