Run-time error 13 Type mismatch~ when Application.Inputbox Cancel

Question

I've read a few questions & articles about this issue, though since I'm a total beginner, I couldn't figure out my personal solution.

I need to exit sub when the user clicks cancel on the InputBox form. In adddition, I need the InputBox to accept the input value.

    Dim UserCol As String
    Dim FirstRow As Integer

    UserCol = Application.InputBox(Prompt:="In what Column do we search? (E.g. enter: A)", Type:=2)
    If UserCol = False Then Exit Sub
' On cancel works OK. But if you type "A" (no quotes) u get a run-time ERROR 13!    

    FirstRow = Application.InputBox(Prompt:="What is your data-table's first row? (E.g. enter: 2)", Type:=1)
    If FirstRow = False Then Exit Sub
' On both cancel & input works flawlessly.

I tried to remove Type := 2 but there was no change.

varocarbas · Accepted Answer · 2013-08-02T12:28:59.367

You cannot treat strings as booleans (what you are doing). A string can output a true/false result but not as you are doing it. Try with this code:

  Dim UserCol As String
  Dim FirstRow As Integer

  UserCol = Application.InputBox(Prompt:="In what Column do we search? (E.g. enter: A)", Type:=2)
  If Len(Trim(UserCol)) < 1 Then Exit Sub
' On cancel works OK. But if you type "A" (no quotes) u get a run-time ERROR 13!

  FirstRow = Application.InputBox(Prompt:="What is your data-table's first row? (E.g. enter: 2)", Type:=1)
  If FirstRow < 1 Then Exit Sub

The first condition is false (and the Sub is exited) if the length of the ("trimmed") input string is lower than 1. The second condition, if the input string is not a number.

NOTE: bear in mind that the reason why the second condition does not trigger an error is because integers do "support boolean"; although it does not have any real meaning here: if you delete this condition nothing would change. My condition checks for what you really want (row being greater or equal than 1). Also remember that InputBox supports integers but this is not usually the case (with most of the controls of this type, you would have to get the inputs as string and convert them into integer; expressly or implicitely).

UPDATE -

Coude to account for Cancel-button clicks:

   Dim UserCol As String
   Dim FirstRow As Integer

   UserCol = Application.InputBox(Prompt:="In what Column do we search? (E.g. enter: A)", Type:=2)

   If (LCase(UserCol) <> "false") Then

     If Len(Trim(UserCol)) < 1 Then Exit Sub
     ' On cancel works OK. But if you type "A" (no quotes) u get a run-time ERROR 13!

     FirstRow = Application.InputBox(Prompt:="What is your data-table's first row? (E.g. enter: 2)", Type:=1)
     If (FirstRow < 1) Then Exit Sub
  End If

If the first InputBox is cancelled a "False" is returned (as string) and if the second one is cancelled a 0 is returned (and thus the original condition can deal with that).

Thanks for the reply, now it accepts the value though it doesn't exit sub if I press ESC or click the Cancel button. Your Second condition Quits the Sub. — Anton Frolov, Aug 02 '13 at 12:10
@AntonFrolov This wasn't part of your answer (and thus, actually speaking, I am not sure why you have unset this answer as the right one, as far as it replies what you asked). You cannot do a multi-question, but you should post a new question per problem. In any case, this seems pretty straightforward and will take a look at it, but you shouldn't have done that (unassigned a right answer to ask a new thing). — varocarbas, Aug 02 '13 at 12:13
Since I'm a newbie here I wasn't really sure how to reach you on this topic. I am sorry for that. ==> I need to exit sub when the user clicks cancel on the InputBox form. In adddition, I need the InputBox to accept the input value. <== My code did 'Exit Sub' but didn't accept the value. You explained why. Thank you =) Your code accepts the value but, it doesn't Exit Sub =/ — Anton Frolov, Aug 02 '13 at 12:18
@AntonFrolov no problem. Take a look at my updated code and understand what is happening (and what your code does). You might have do that by your own (just setting a break point and looking for the values when pressing "Cancel"). Test this code and let me know if you have any other question (but not a completely different one, please). — varocarbas, Aug 02 '13 at 12:31
@AntonFrolov PS: remember that if you write the code and ask to solve a specific problem (I or any other answerer) will answer this specific problem (and, perhaps, correct clearly wrong parts), but we assume that your code delivers what you want. You cannot expect a person not knowing even what you want this code for to account for any eventuality. — varocarbas, Aug 02 '13 at 12:35

Run-time error 13 Type mismatch~ when Application.Inputbox Cancel

1 Answers1