vba 验证用户表单文本框条目的格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24889340/
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
Validating the format of a userform textbox entry
提问by Lewis Heslop
I have a UserForm for a database data extractor I'm making. In it there is a TextBox for typing in the part number that the user wants to extract data for. I want to validate that the user has entered the correct format of part number before the bulk of the extractor runs. To do this I need a code to validate that the text is entered in the specific format:
我有一个用于我正在制作的数据库数据提取器的用户表单。其中有一个文本框，用于输入用户想要为其提取数据的部件号。我想在大量提取器运行之前验证用户是否输入了正确的部件号格式。为此，我需要一个代码来验证文本是否以特定格式输入：
3 Numeric Characters 1 Alphabetic Character or 1 Hyphon then 5 Numeric Characters
3 个数字字符 1 个字母字符或 1 个 Hyphon 然后 5 个数字字符
I tried the following validations at first:
我首先尝试了以下验证：
'validate that box is not empty
If TextBox1.Value = "" Then
MsgBox ("Sorry, you need to provide an Amount")
TextBox1.SetFocus
Exit Sub
End If
'validate that box is numeric
If Not IsNumeric(TextBox1.Value) Then
MsgBox ("Sorry, must enter a numer")
TextBox1.SetFocus
Exit Sub
End If
But then I realised that I had the problem that there may be an alphabetic char or hyphon in the fourth position.
但后来我意识到我遇到的问题是第四个位置可能有一个字母字符或连字符。
I would appreciate any suggestions.
我将不胜感激任何建议。
Thanks in advance.
提前致谢。
回答by David Zemens
A beginner way to check this input is to just chop up the input string and compare the parts as needed:
检查此输入的初学者方法是将输入字符串切碎并根据需要比较部分：
Const alpha as String = "abcdefghijklmnopqrstuvwxyz-"
Dim strValue as String
Dim msg as String
strValue = TextBox1.Value
'Make sure it's the right LENGTH
If Len(strValue) <> 9 Then
msg = "Please enter the ID as 3 numeric, 1 alpha/hyphen, 5 numeric"
GoTo EarlyExit
End If
'Check the first three for numeric:
If Not IsNumeric(Left(strValue), 3) Then
msg = "The first three characters should be numeric"
GoTo EarlyExit
End If
'Check the middle character, assuming case not sensitive:
If Instr(1, alpha, Lcase(Mid(strValue, 4, 1)) = 0 Then
msg = "The fourth character should be hyphen or alphabet"
GoTo EarlyExit
End If
'Check the last four characters
If Not IsNumeric(Right(strValue, 4)) Then
msg = "The last four characters should be numeric"
GoTo EarlyExit
End If
'If you've gotten here, then the input is validated:
Exit Sub
EarlyExit:
MsgBox msg
TextBox1.SetFocus
End Sub
3 Numeric Characters 1 Alphabetic Character or 1 Hyphon then 5 Numeric Characters
3 个数字字符 1 个字母字符或 1 个 Hyphon 然后 5 个数字字符
