Force InputBox to enter positive integer

[edtully]

I want to have an input box open up and ask the user to enter the number of items. I want to force this to be a positive integer. The following code (sort of) works:

While Not (no_of_items > 0)
no_of_items = InputBox("How many items?")
Wend

Two problems with this code
1) If the user enters a decimal it will accept it even though I want it to only accept integers.

2)If the user enters a string (ex: "two") then it will crash due to a type mismatch error since it is trying to evalute the no_of_items>0 statement with a string. If the user enters a string I would like to repeat the question until an integer is entered.

Any suggestion? Thanks in advance.

 

[phantom1975]

WELCOME TO THE BOARD!

You can do this:

Dim no_of_items as Integer

On Error Resume Next
no_of_items = InputBox("How many items?")
If Err.Number <> 0 Then
MsgBox "You need to enter a whole number greater than zero. Please try again"
End If


Does this help?

 

[Richie(UK)]

Hi,

The second part of your problem could be solved by making use of the IsNumeric function. This returns True if a numeric value is entered.

Not sure about the first problem. How about using a UserForm and SpinButtons instead and incrementing only in whole numbers?

 

[Seti]

How about

no_of_items = TRUNC(no_of_items)

to test for integer once you have a number and it is poisitve.

 

[edtully]

Thanks for the replies.

I used Phantom1975's suggestion as a building block. The only problems were that it allowed a decimal to be entered (in which case it rounded the number to an integer but still accepted it) and it allowed negative numbers to be entered. Here is my code if anyone is curious.

Dim no_of_items As Single
flag = 1
While flag = 1
On Error Resume Next
no_of_items = InputBox("How many items?")
If (Err.Number <> 0 Or no_of_items < 1) Or (Int(no_of_items) <> no_of_items) Then
MsgBox "You need to enter a whole number greater than zero. Please try again"
flag = 1
Else
flag = 0
End If
Wend

So I read in the number and then check for 1) an error (it is not a real)
2) If it is less than 1 (only want positive integers)
3) If the integer portion equals the whole variable. This check eliminates any decimal entries.

If any of these fail the flag is set and the user is given the error message and then is prompted again to make the entry. Othersise it sets the flag to 0 and exits.

 

[phantom1975]

As far as I can tell, your code looks great. The only thing that you might want to consider is changiing your data type to Integer instead of Single. This way if someone enters a decimal it will error instead of round the number. I see that your code already checks the rounded number against the value of the variable. Ideally, that should take care of the problem. When I am troubleshooting, I try to change one thing at a time so that I can get at least a part of my code to work and then see where the faulty code is. At any rate, that's just my $0.02.

 

[edtully]

[No message]

 

[edtully]

Thanks for the comment.

The problem with using an integer type is that it will not error if a real number is entered. For example, if you enter 4.5 it will just set no_of_items=4 and not give an error so you don't know if they entered an integer or decimal.

 

[phantom1975]

Duh...that's right. The alternative would be to search the variable for a decimal point. If a decimal point is present, then you could have the MsgBox appear.

 

[BoReD]

Duh...that's right. The alternative would be to search the variable for a decimal point. If a decimal point is present, then you could have the MsgBox appear.

hi there, a beginner user of VBA here..can i noe how to search the variable for a decimal point? cant seem to find the 'search' thanx a lot