MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Inputbox


Posted by Roy Brunt on August 17, 2001 3:32 PM

How do i set what values can be put into an inputbox.

I only want the user to be able to enter a number between 1 and 300 and nothing else to be accepted.

Thanks in advance

Roy


Posted by Aladin Akyurek on August 17, 2001 3:51 PM

I reckon you mean an "inputbox" created by using the data validation option. If so,

Choose "Whole Number" for "Allow" on Data Validation.

Enter 1 for "Minimum" and 300 for "Maximum".

Aladin

Posted by Roy Brunt on August 17, 2001 3:56 PM

It is an inputbox generated in vb code

Can you help

Posted by Aladin Akyurek on August 17, 2001 4:00 PM

For that one you need a VBA programmer. (NT)

Posted by Ivan F Moala on August 17, 2001 4:18 PM

You are better off using the application.inputbox
method as you can specify the type of input
which in this case is a number.
Have a look @ example;

Sub ValadateInput()
Dim Ans As Double

Ans = Application.InputBox("Enter value between 1 - 300", Type:=1)
Select Case Ans
Case 1 To 300
MsgBox "You entered a valid number:= " & Ans
Case 0
MsgBox "You cancelled or " & Ans & " is not in the Valid range"
Case Else
MsgBox Ans & " is not in the Valid range"
Exit Sub
End Select


'Your code here

End sub


Ivan

Posted by Robb on August 17, 2001 4:19 PM

Roy

You need to test the value returned from the inputbox. Try something like this:

GetNumber: 'This line must be included
myin = InputBox("Enter a number between 1 and 300") ' This may be whatever you have as an InputBox
myN = CDbl(myin)
If myN < 1 Or myN > 300 Then
MsgBox "Must be between 1 and 300 - try again"
GoTo GetNumber
Else
End If

You will have used different names for your variables, but you should get the idea.

Does this help?

Posted by Ivan F Moala on August 17, 2001 11:26 PM

Robb
Should really use the application.inputbox method
as the routine given will fail on;
1) User cancel
2) User types in Text
You can put further checks to overcome this
BUT easier to let Excel do this Via the above
method.

Ivan Roy You need to test the value returned from the inputbox. Try something like this: GetNumber: 'This line must be included