Search code examples
excelvbainputbox

Rerun InputBox when there is a wrong variable type input error

My goal is to rerun the InputBox when it crashes and give the user another chance to give the right type of input. (Like it crashes, you get a msgbox where it says "sorry your entry is not available, please retry", and it would jump back to the InputBox.)

Test1:
qm = InputBox("Wie viele Quadrat Meter hat die Wohnung?" & vbLf & "Bitte geben sie die QM Zahl an.", Angabe)
If IsError(qm) Then GoTo Test1

qm is defined as an Integer, =0, and below there is a Select Case mutilple alternative which changes qm to a number from 1-600.

When I type in text like "Hey guys", Excel gives me error 13 (Runtime error 13': type mismatch).

Solution

  • That's why in VBA you can use the Application.InputBox with a 4th parameter Type. If you use Type:=1 then only Numeric values are allowd.

    Code:

    Dim qm As Integer

qm = Application.InputBox("Wie viele Quadrat Meter hat die Wohnung?" _
            & vbLf & "Bitte geben sie die QM Zahl an.", "Angabe", Type:=1)