如何制作一个 VBA 输入框，该输入框仅在用户输入包含 7 个字符的代码(包含 3 个数字和一个"M"和一个"!"时消失)

Question

我是VBA的新手，我试图创建一个具有参数的类似密码的输入框。只能有7个字符，其中3个是数字，1个是"B"还有一个是"?"我现在正在想办法弄到这三个数字，如果有任何帮助，我将不胜感激。这是我到目前为止写的

Sub btn_CheckCode2()
Dim code As String
code = InputBox("Enter a Code")
Dim threenumbers As Boolean
threenumbers = False
Do Until code <> "" And Len(code) = 7 And threenumbers = True
If IsNumeric(Right(code, 1)) = True And IsNumeric(Right(code, 2)) = True And IsNumeric(Right(code, 3)) = True Then
threenumbers = True
code = InputBox("Enter a Code")
IsNumeric(code) = True
Loop

MsgBox ("Thank you for entering " & code)
End Sub

Answer 1

使用StrConv将字符串转换为字节数组，然后循环数组并计数，如果值在48 - 57(即数字0 -9)(参考)内，如果计数为3，则应该意味着输入中有3个数字。

我们可以应用类似的概念来检查1个"B"还有一个" "的实例。函数InputValid执行上述操作，然后您可以在循环中使用它来验证输入，如下所示:

Sub btn_CheckCode2()
Dim code As String
code = InputBox("Enter a Code")

Do Until InputValid(code)
code = InputBox("Enter a Code")
Loop

MsgBox ("Thank you for entering " & code)
End Sub
Function InputValid(argInput As String) As Boolean
Const limitNum As Long = 3
Const limitB As Long = 1
Const limitExclaimation As Long = 1

If Len(argInput) <> 7 Then

Dim inputBytes() As Byte
inputBytes = StrConv(argInput, vbFromUnicode)

Dim countNum As Long
Dim countB As Long
Dim countExclaimation As Long

Dim i As Long
For i = 0 To UBound(inputBytes)
Select Case inputBytes(i)
Case 33
countExclaimation = countExclaimation + 1
Case 48 To 57
countNum = countNum + 1
Case 66
countB = countB + 1
End Select

If countNum = limitNum And _
countB = limitB And _
countExclaimation = limitExclaimation Then
InputValid = True
End If
Next i
End If
End Function

编辑根据GSerg的建议，这是另一种方法:

Sub btn_CheckCode2()
Dim code As String
code = InputBox("Enter a Code")

Do Until InputValid(code)
code = InputBox("Enter a Code")
Loop

MsgBox ("Thank you for entering " & code)
End Sub
Function InputValid(argInput As String) As Boolean
Const limitNum As Long = 3
Const limitB As Long = 1
Const limitExclaimation As Long = 1
Const limitInputLen As Long = 7
If Len(argInput) <> limitInputLen Then

Dim countNum As Long
Dim countB As Long
Dim countExclaimation As Long

Dim i As Long
For i = 1 To Len(argInput)
Select Case Mid$(argInput, i, 1)
Case "!"
countExclaimation = countExclaimation + 1
Case 0 To 9
countNum = countNum + 1
Case "B"
countB = countB + 1
End Select

If countNum = limitNum And _
countB = limitB And _
countExclaimation = limitExclaimation Then
InputValid = True
End If
Next i
End If
End Function