Runtime Error '13' Type Mismatch clicking Cancel on VBA Input Box-CodePudding

I am using an input box to pass a value to a stored procedure. However, when I click cancel I get a Runtime Error '13' Type Mismatch error. I tried to include an exit sub command but it hasn't worked. Code below. Any help would be appreciated. Thanks.

Option Explicit

Sub reverse_posted()
Const PROC = "ashcourt_concrete_balfour_reverse_posting"

Dim con As ADODB.Connection, cmd As ADODB.Command, i As Long
i = InputBox("Invoice Number to be re-posted")
If Len(i) < 1 Then Exit Sub
Set con = New ADODB.Connection
con.Open "Provider=SQLOLEDB;Data Source=ashcourt_app1;" & _
         "Initial Catalog=ASHCOURT_Weighsoft5;" & _
         "Integrated Security=SSPI;Trusted_Connection=Yes;"

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = con
    .CommandType = adCmdStoredProc
    .CommandText = PROC
    .Parameters.Append .CreateParameter("P1", adInteger, adParamInput)
    .Execute , i
End With

con.Close
Set con = Nothing

MsgBox ("Invoice Number " & i & " reversed")

End Sub

CodePudding user response：

InputBox returns a string.
When you press Cancel it returns "" which isn't a number, so you get the error (you'd get the same error if you entered text and hit OK).

Maybe use two variables - i As String and lngI As Long.

Dim i As String, lngI As Long
i = InputBox("Invoice Number to be re-posted")
If IsNumeric(i) Then
    lngI = CLng(i)
Else
    Exit Sub
End If

CodePudding user response：

An InputBox always returns text, thus an empty string when cancelled, so use Val:

i = Val(InputBox("Invoice Number to be re-posted"))
If i = 0 Then Exit Sub