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

You can use the Application.InputBox version of VBA, see my answer below — Shai Rado
– Shai Rado, Commented Sep 13, 2017 at 8:15

Shai Rado · Accepted Answer · 2017-09-13 08:12:37Z

2

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)

answered Sep 13, 2017 at 8:12

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mentos Over a year ago

Thanks a lot for editing my question so that it is more readable. The Allpication.Inputbox worked great and I will try to take your editing as an example for future posts and answers.

Mrig · Accepted Answer · 2017-09-13 08:15:08Z

2

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

edited Sep 13, 2017 at 8:15

answered Sep 13, 2017 at 8:07

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

1 Comment

Mentos Over a year ago

Thank you. The IsNumeric() solution was gives for my task as a tip but I am glad you showed me how to use it correctly.

Prisoner · Accepted Answer · 2017-09-13 08:06:55Z

1

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

answered Sep 13, 2017 at 8:06

Prisoner

1,8573 gold badges24 silver badges41 bronze badges

Comments

FunThomas · Accepted Answer · 2017-09-13 08:08:46Z

1

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

answered Sep 13, 2017 at 8:08

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

1 Comment

Mentos Over a year ago

Man this is, for me, a smart solution. Thanks a lot.

Trimax · Accepted Answer · 2017-09-13 08:11:19Z

1

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

answered Sep 13, 2017 at 8:11

Trimax

2,4739 gold badges39 silver badges62 bronze badges

Collectives™ on Stack Overflow

Rerun InputBox when there is a wrong variable type input error

5 Answers 5

1 Comment

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related