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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

'if nothing entered, stop the procedure

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

'if a bad entry found, take action

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

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

Please Login or Register  to view this content.

[johnjohns]

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

rgds

johnjohns