vba 输入框问题

Trouble with InputBoxes

提问by Katana24

I'm currently working with InputBoxes in MS Access VBA. I'm examining validation and handling how the user interacts with the InputBox through pressing the OK or Cancel buttons.

我目前正在使用 MS Access VBA 中的 InputBoxes。我正在检查验证和处理用户如何通过按下 OK 或 Cancel 按钮与 InputBox 交互。

Correct me if I'm wrong but InputBoxes can return any data type and by default return a string? For example:

如果我错了，请纠正我，但 InputBoxes 可以返回任何数据类型并且默认返回一个字符串？例如：

Dim userInputValue As String

'Text to display, Title, Default Value
userInputValue = InputBox("Please enter a #", "Determine Limit", 10000)

If userInputValue = "" Then
    MsgBox ("You pressed the cancel button...")
End If

If the user presses the Cancel button this will run fine.

如果用户按下取消按钮，这将运行良好。

But when I swap this for an integer value like so:

但是当我把它换成一个像这样的整数值时：

Dim userInputValue As Integer
'Text to display, Title, Default Value
userInputValue = InputBox("Please enter a #", "Determine Limit", 10000)

If userInputValue = 0 Then
    MsgBox ("You pressed the cancel button...")
End If

I receive a Type Mismatch: Runtime Error '13'Why is this? When I debug the code and look at what is being returned I find that the userInputValueis actually 0, which is what I'm checking for. So is the problem that the InputBox is actually returning a string?

我收到了Type Mismatch: Runtime Error '13'这是为什么？当我调试代码并查看返回的内容时，我发现userInputValue实际上是 0，这就是我要检查的内容。那么问题是 InputBox 实际上是返回一个字符串吗？

采纳答案by Siddharth Rout

When in doubt, check the inbuilt VBA help ;)

如有疑问，请查看内置的 VBA 帮助；)

InputBox()returns a String

InputBox()返回一个字符串

You can try this for Integers

你可以试试这个整数

Sub Sample()
    Dim Ret As String, userInputValue As Integer

    'Text to display, Title, Default Value
    Ret = InputBox("Please enter a #", "Determine Limit", 10000)

    If Ret = "" Then
        MsgBox ("You pressed the cancel button... or you pressed OK without entering anything")
    Else
        If IsNumeric(Ret) Then
            userInputValue = Val(Ret)
        Else
            MsgBox ("Incorrect Value")
        End If
    End If
End Sub

回答by Alex K.

Here is a way to catch most outcomes of interacting with the dialog;

这是一种捕获与对话交互的大多数结果的方法；

Dim value As String
value = InputBox("Please enter a #", "Determine Limit", 10000)

If (StrPtr(value) = 0) Then
    MsgBox "You pressed cancel or [X]"

ElseIf (value = "") Then
    MsgBox "You did not enter anything"

ElseIf (Val(value) = 0 And value <> "0") Then
    MsgBox "Invalid number"

Else
    MsgBox "You entered " & value

End If

回答by HansUp

InputBoxreturns a string regardless of the number the user enters. If they click Cancel, it returns an empty string.

InputBox无论用户输入的数字如何，都返回一个字符串。如果他们单击取消，则会返回一个空字符串。

Try this in the Immediate window.

在“立即”窗口中尝试此操作。

? TypeName(InputBox("Please enter a #", "Determine Limit", 10000))
String

For the test in your code, check whether the numerical equivalent of userInputValueis equal to zero.

对于代码中的测试，请检查 的等价数字是否userInputValue等于 0。

If Val(userInputValue) = 0 Then
    MsgBox ("You pressed the cancel button...")
End If

Note than InputBoxdoesn't allow you to distinguish whether the user clicked Cancel or deleted the starting value (10000) and clicked OK. Either way, InputBoxreturns an empty string (""). And Val("")also returns zero. If that will be a problem, substitute a custom form to gather the user input ... which is not as limited as InputBox.

注意InputBox不允许您区分用户是单击取消还是删除了起始值 (10000) 并单击了确定。无论哪种方式，都InputBox返回一个空字符串 ("")。并且Val("")还返回零。如果这将是一个问题，请替换自定义表单来收集用户输入......它不像InputBox.

回答by swmcdonnell

Note: The following applies only to Excel. The Application.InputBox function is not available in Access:

注意：以下仅适用于 Excel。Application.InputBox 函数在 Access 中不可用：

Application.InputBoxreturns "False"if the user clicks Cancel.

如果用户单击取消，Application.InputBox将返回“False”。

Dim userInputString As String
Dim userInputValue As Integer
'Text to display, Title, Default Value
userInputString = Application.InputBox("Please enter a #", "Determine Limit", 10000)
If userInputString = "False" Then
    MsgBox ("You pressed the cancel button...")
Else
    userInputValue = CInt(Trim(userInputString))
End If

回答by Lingo

Rather than getting back a number back from InputString, set the default string to "___________". Then I can test if anything was entered and the Cancel" key will return a null string.

与其从 InputString 取回一个数字，不如将默认字符串设置为“___________”。然后我可以测试是否输入了任何内容，Cancel”键将返回一个空字符串。

I also add a test value "Allow_Empty_String" (True/False) because sometimes I want an empty string back.

我还添加了一个测试值“Allow_Empty_String”（真/假），因为有时我想要一个空字符串。

The "Flag_Msg_Err" has to be reset to False because "Msg_Err" sets it to true to catch calls from loops.

"Flag_Msg_Err" 必须重置为 False，因为 "Msg_Err" 将其设置为 true 以捕获来自循环的调用。

The routine needs to respond in one of 3 ways: 1. If the "Cancel" button is pressed, the program ends. 2. If the "OK" button is pressed, IF ALLOW_EMPTY_STRING THEN a message is given and input starts again ELSE an empty string is returned END IF 3. That string was entered., then TRIM(STRING) is returned.

该例程需要以三种方式之一进行响应： 1. 如果按下“取消”按钮，则程序结束。2. 如果按下“确定”按钮，则 IF ALLOW_EMPTY_STRING THEN 给出消息并再次开始输入 ELSE 返回空字符串 END IF 3. 输入该字符串。然后返回 TRIM(STRING)。

Code:

代码：

`Function Input_String(Prog, Message, Optional Allow_Empty_String = False) ' Returns a String or the word "Cancal'". ' 2/28/19 Created. WML

`Function Input_String(Prog, Message, Optional Allow_Empty_String = False) ' 返回一个字符串或单词“Cancal”。' 2/28/19 创建。WML

If Trace Then Prog = Prog & "(*)"
Call Name_Put("Flag_Msg_Err", False)

Do
    Test_String = "_____________"
    Input_String = InputBox(Prompt:=Message, _
                            Default:=Test_String, Title:=Prog)

    Select Case Input_String

        Case "TRACE"
            ' for Debugging
            Old_Trace = Named("Flag_Trace")
            New_Trace = Not Old_Trace
            Call Name_Put("Flag_Trace", New_Trace)
            Msg = "TRACE is now set to " & New_Trace & "."
            Call Name_Put("Flag_Msg_Err", False)

        Case Test_String
            If Allow_Empty_String Then
                Msg = "You must enter something,|" & _
                      " or select CANCEL."
                Call Msg_Err(Prog, Msg, , True)
                Call Name_Put("Flag_Msg_Err", False)
            Else
                Input_String = ""
                Finished = True
            End If

        Case ""
            If Trace Then
               Stop: Exit Function
            Else
               End
            End

        Case Else
            ' If entered a space or clicked "Ok".
            Input_String = Trim(Input_String)
            Finished = True

    End Select

 Loop

End Function ' Input_String`

结束函数“Input_String”