vba 验证输入框的输入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21976831/
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
Validate entry of an input box
提问by Adrian Gornall
Im trying to get an input box to validate the entries a user will make.
我试图获得一个输入框来验证用户将输入的条目。
i'm using the below script but cant get the validation to work, any help would be appreciated.
我正在使用下面的脚本，但无法使验证工作，任何帮助将不胜感激。
Sub inputbox()
Dim Manager As Long
On Error Resume Next
Application.DisplayAlerts = False
Manager = Application.inputbox(Prompt:="Please enter a manager.", Title:="Pick A Manager Name", Type:=1)
On Error GoTo 0
Application.DisplayAlerts = True
If Manager = "" Then
Exit Sub
ElseIf Manager <> Ben, Cameron, Chris, Martin, Peter Then
MsgBox "Incorrect Name, pick a new one!"
Else
MsgBox "Your input was " & Manager
End If
End Sub
回答by PatricK
Although a Sub name same as built in ones are not recommended, you can do what you are after like below.
虽然不推荐使用与内置名称相同的子名称，但您可以按照以下方式进行操作。
First you need to change the InputBox Typeto 2(String), since you are comparing with String. Then you should make a function to check if the input is part of a Manager List.
首先，您需要将 InputBox Type更改为2(String)，因为您正在与 String 进行比较。然后你应该创建一个函数来检查输入是否是经理列表的一部分。
Sub inputbox()
On Error Resume Next
Dim Manager As String
Manager = Application.inputbox(Prompt:="Please enter a manager name:", Title:="Pick A Manager Name", Type:=2)
If Manager <> "" Then
If IsManager(Manager) Then
MsgBox "Your input was " & Manager
Else
MsgBox "Incorrect Name, pick a new one!"
End If
End If
End Sub
Private Function IsManager(sTxt As String) As Boolean
Dim aManagers As Variant, oItem As Variant, bAns As Boolean
aManagers = Array("Ben", "Cameron", "Chris", "Martin", "Peter")
bAns = False
For Each oItem In aManagers
If LCase(oItem) = LCase(Trim(sTxt)) Then
bAns = True
Exit For
End If
Next
IsManager = bAns
End Function
UPDATE (Improved version suggested by Simon1979):
更新（Simon1979 建议的改进版本）：
Private Function IsManager(sTxt As String) As Boolean
On Error Resume Next
Dim aManagers As Variant
aManagers = Array("Ben", "Cameron", "Chris", "Martin", "Peter")
IsManager = Not IsError(Application.WorksheetFunction.Match(Trim(sTxt), aManagers, 0))
End Function
回答by Simon1979
Haven't used the InputBox with Excel but I imagine it will be very similar to the Access one. I use the below method to validate inputbox:
没有在 Excel 中使用 InputBox，但我想它会与 Access 非常相似。我使用以下方法来验证输入框：
Dim strM as string
EnterManager:
strM = InputBox("Enter Manager.")
If StrPtr(strM) = 0 Then 'Cancel was pressed
' Handle what to do if cancel pressed
Exit Sub
ElseIf Len(strM) = 0 Then 'OK was pressed with nothing entered
MsgBox "You must enter a Manager."
GoTo EnterBuyer
End If
To add your criteria you could add on another If, I'm not sure you can use the approach you have for checking the list of names. Also don't understand how you compare a long Managerwith a list of names Ben, Cameron, Chris, Martin, Peter, unless they are assigned variables, in which case I would suggest adding prefixes so it is more obvious such as lBenas opposed to strBenso you can easily see the difference in variable type.
要添加您可以添加的条件If，我不确定您是否可以使用检查名称列表的方法。也不明白如何将 longManager与名称列表进行比较Ben, Cameron, Chris, Martin, Peter，除非它们被分配了变量，在这种情况下，我建议添加前缀，这样它就更明显了，例如lBen与相反，strBen这样您就可以轻松看到变量类型的差异.
If strM <> "Ben" And strM <> "Cameron" And strM <> "Chris" And strM <> _
"Martin" And strM <> "Peter" Then
MsgBox "Incorrect Name, pick a new one!"
Else
MsgBox "Your input was " & strM
End If
