Force InputBox to enter positive integer

edtully

New Member
Joined
Aug 14, 2002
Messages
6
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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

Well-known Member
Joined
May 19, 2002
Messages
2,916
How about

no_of_items = TRUNC(no_of_items)

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

edtully

New Member
Joined
Aug 14, 2002
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

New Member
Joined
Aug 14, 2002
Messages
6

ADVERTISEMENT

[No message]
 

edtully

New Member
Joined
Aug 14, 2002
Messages
6
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

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

Board Regular
Joined
Jul 24, 2005
Messages
61
phantom1975 said:
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
 

Forum statistics

Threads
1,147,681
Messages
5,742,587
Members
423,739
Latest member
plkdti

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top