无法正确取消Application.Inputbox(Can't Cancel Application.Inputbox Properly)

无法正确取消Application.Inputbox(Can't Cancel Application.Inputbox Properly)

因此，此代码一直有效，直到您决定点击Cancel或关闭带有X的输入框窗口，此时它会为您提供：

运行时错误'424'：

所需对象

然后它突出显示调试中的这部分代码：

Set ranC = Application.InputBox("Select the Cal B table.", Type:=8)

我似乎无法使用零字符串长度字符串测试来取消此应用程序。 我需要能够关闭当前工作簿，显示用户窗体并退出子。

这是我的代码:(只要您选择了某些内容并且不取消或关闭，代码就可以运行）

Sub popCheckVals() Dim ranC As Range, calBC(1 To 39) As Variant, i As Integer, j As Integer, k As Integer, l As Integer dozerCal.Hide Set ranC = Application.InputBox("Select the Cal B table.", Type:=8) l = 1 For j = 1 To 13 For i = 1 To 3 calBC(l) = ranC(j, i) l = l + 1 Next Next mltn = calBC(1) mlte = calBC(2) mltelev = calBC(3) rltn = calBC(4) rlte = calBC(5) rltelev = calBC(6) mrtn = calBC(10) mrte = calBC(11) mrtelev = calBC(12) rrtn = calBC(13) rrte = calBC(14) rrtelev = calBC(15) smltn = calBC(22) smlte = calBC(23) smltelev = calBC(24) srltn = calBC(25) srlte = calBC(26) srltelev = calBC(27) smrtn = calBC(31) smrte = calBC(32) smrtelev = calBC(33) srrtn = calBC(34) srrte = calBC(35) srrtelev = calBC(36) ActiveWorkbook.Close dozerCal.Show End If End Sub

So this code works until you decide to hit Cancel or close the inputbox window with the X at which point it gives you:

Run-time error '424':

Object required

and then it highlights this part of the code in debug:

Set ranC = Application.InputBox("Select the Cal B table.", Type:=8)

I can't seem to use the zero string length string test for canceling with this application. I need to be able to close the current workbook, show a userform and exit the sub.

Here is my code: (the code works as long as you select something and don't cancel or close)

Sub popCheckVals() Dim ranC As Range, calBC(1 To 39) As Variant, i As Integer, j As Integer, k As Integer, l As Integer dozerCal.Hide Set ranC = Application.InputBox("Select the Cal B table.", Type:=8) l = 1 For j = 1 To 13 For i = 1 To 3 calBC(l) = ranC(j, i) l = l + 1 Next Next mltn = calBC(1) mlte = calBC(2) mltelev = calBC(3) rltn = calBC(4) rlte = calBC(5) rltelev = calBC(6) mrtn = calBC(10) mrte = calBC(11) mrtelev = calBC(12) rrtn = calBC(13) rrte = calBC(14) rrtelev = calBC(15) smltn = calBC(22) smlte = calBC(23) smltelev = calBC(24) srltn = calBC(25) srlte = calBC(26) srltelev = calBC(27) smrtn = calBC(31) smrte = calBC(32) smrtelev = calBC(33) srrtn = calBC(34) srrte = calBC(35) srrtelev = calBC(36) ActiveWorkbook.Close dozerCal.Show End If End Sub

最满意答案

当用户单击“ Cancel按钮时， InputBox返回False ，这不是Range对象，也不能分配给ranC。 处理此问题的一种方法是将这部分代码包装在错误处理程序中：

On Error Resume Next Set ranC = Application.InputBox("Select the Cal B table.", Type:=8) If Err.Number = 424 Then ' Handle cancel button Debug.Print "User cancelled" Exit Sub ElseIf Err.Number <> 0 Then ' Handle unexpected error Debug.Print "Unexpected error" Else ' Your code here End If On Error GoTo 0 ' This line could go in the else block

When the user clicks the Cancel button InputBox returns False, which is not a Range object and can't be assigned to ranC. One way to handle this is to wrap this part of the code in an error handler:

On Error Resume Next Set ranC = Application.InputBox("Select the Cal B table.", Type:=8) If Err.Number = 424 Then ' Handle cancel button Debug.Print "User cancelled" Exit Sub ElseIf Err.Number <> 0 Then ' Handle unexpected error Debug.Print "Unexpected error" Else ' Your code here End If On Error GoTo 0 ' This line could go in the else block