how to limit user input between 1 to 1000

[dada540]

I just started learning VBA and I am trying to ask user to input
a number between 1 and 1000, and I wrote the following code:

----------------------------------------------------------------------------------------
Sub Simu()
Dim Iter As Variant
Dim Msg As String
Dim Ans As Integer

EnterAgain:
'if any run time error happens, excute BadEntry

On Error GoTo BadEntry

'Ask user to enter a number between 1 to 1000"

Iter = InputBox("How many iterations do you want? Please enter a number between 1 and 1000", Simulation)

'if nothing entered, stop the procedure

If Iter = "" Then Exit Sub

'if the number entered is not between 1 and 1000, treat it as bad entry

If (Iter < 1 Or Iter > 1000) Then GoTo BadEntry

'if the number entered is valid, call reset function and start procudure
'this part works fine

Call Reset
For i = 1 To Iter
Application.Calculate
Next i
Exit Sub

'if a bad entry found, take action

BadEntry:

'ask the user if they want to enter a valid number again

Msg = "Your have entered an invalid number, you want to enter again?"

'Here is the confusing part...when I test the code, the msgbox always pop up twice
'even I choose yes...

Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then Resume EnterAgain

End Sub

-------------------------------------------------------------------------------------------------------------

Everything works fine except that the yes/no msgbox will always pop up twice
before the inputbox pops up. That means Excel will ask twice if the user want to enter
a number again...Can anyone help?

[mikerickson]

Perhaps something like this

Sub test()
    Dim uiValue As Long
    
    Do
        uiValue = Application.InputBox("Enter a value between 1 and 1000", Type:=1)

        If uiValue < 1 Then
            Rem cancel pressed
            Exit Sub
        End If
    Loop Until uiValue < 1001
    
    MsgBox uiValue
End Sub

[johnjohns]

Also try the data validation. You can restrict the entries and also display input messages and error alerts.

rgds

johnjohns