在执行宏之前，如何输入InputBox？

My macro requires an input as soon as the macro is executed. However, if a user presses cancel, the macro will execute without this information, which leaves holes in the data. Here was my best guess, but it doesn't prevent users from cancelling and still executes:

执行宏后,我的宏需要输入。但是,如果用户按下取消,宏将在没有此信息的情况下执行,这会在数据中留下漏洞。这是我最好的猜测,但它不会阻止用户取消并仍然执行:

        Do Until Not IsEmpty(myWknum)
        DoEvents

            myWknum = InputBox("Please enter week number")

        Loop

3 个解决方案

#1

If, when the user cancels the InputBox, you wish to exit the subroutine without processing anything further, use the Exit Sub statement:

如果,当用户取消InputBox时,您希望退出子例程而不进一步处理任何内容,请使用Exit Sub语句:

Sub Test
    Dim myWkNum As String
    myWkNum = InputBox("Please enter week number")
    If myWkNum = "" Then Exit Sub
    '...
    'other code
    '...
End Sub

or, with some validity testing:

或者,通过一些有效性测试:

Sub Test
    Dim myWkNum As String
    Do
        myWkNum = InputBox("Please enter week number")
        If myWkNum = "" Then
            Exit Sub
        ElseIf IsNumeric(myWkNum) Then
            If myWkNum > 0 And myWkNum < 54 And Int(myWkNum) = myWkNum Then
                Exit Do
            End If
        End If
    Loop
    '...
    'other code
    '...
End Sub

#2

This will force a value, and that value must be a number:

这将强制一个值,该值必须是一个数字:

Sub t()
Dim myWknum As String

Do While myWknum = "" Or Not IsNumeric(myWknum)
    myWknum = InputBox("Please enter week number")
Loop
End Sub

And this, a little more verbose (and can probably be made more efficient), will force the user to input a number from 1 to 52:

而且,更加冗长(并且可能更高效),将强制用户输入1到52之间的数字:

Sub t()
Dim myWknum As String

Do While myWknum = "" Or Not IsNumeric(myWknum)
    myWknum = InputBox("Please enter week number")
Loop

If myWknum < 1 Or myWknum > 52 Then
    Do While myWknum < 1 Or myWknum > 52
        myWknum = InputBox("Please use a valid week number. (>= 1 or <= 52)")
    Loop
End If

End Sub

#3

add this before the Loop line.

在Loop行之前添加它。

if myWknum="" then Exit Do

如果myWknum =“”则退出Do

#1