验证用户表单文本框条目的格式

Question

我正在制作一个数据库数据提取器的UserForm。其中有一个文本框，用于输入用户想要提取数据的零件号。我想在提取程序运行之前验证用户是否输入了正确的零件号格式。为此，我需要一个代码来验证文本是否以特定格式输入：

3个数字字符1个字母字符或1个连字符，然后是5个数字字符

我首先尝试了以下验证：

'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 

但后来我意识到我遇到了一个问题，在第四个位置可能有一个字母字符或hyphon。

我将不胜感激任何建议。

提前谢谢。

Answer 1

检查此输入的初学者方法是将输入字符串切碎，并根据需要比较各部分：

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个数字字符1个字母字符或1个连字符，然后是5个数字字符