Rerun InputBox when there is a wrong variable type input error

Question

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).

Answer 1

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)

Answer 2

You can replace If IsError(qm) Then GoTo Test1 with On Error GoTo Test1, and On Error GoTo 0 to reset the error handler.

Details can see https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/on-error-statement

Answer 3

Try something like below

Sub Demo()
    Dim qm As String
    qm = 0
Test1:
    qm = InputBox("Enter value")
    If Not IsNumeric(qm) Then GoTo Test1
End Sub

Declare qm as String and check whether its numeric or not. Then you'll have to convert String into Integer using CInt().

Answer 4

There are various ways to solve this. I would suggest to define a variable as variant (can hold any datatype) and check if it is numeric:

dim answer as variant, qm as integer
do while true
    answer = InputBox(...)
    if isNumeric(answer) then
        qm = cInt(answer)
        exit do
    endif
loop

Answer 5

You can set an event to validate de content of the TextBox every time it changes. I checked that is a number, but you can validate any other condition you need.

Private Sub TextBox1_Change()
  validator = IsNumeric(TextBox1.Value)
  If validator = False Then
    MsgBox "WARNING! You must enter a number!"
    TextBox1.BackColor = &HFF8&
  End If
End Sub