You can hide these values in another sheet somewhere, then compare the input
value to the list. If it's not found, then make the user enter a valid
number.
I'd use a Do loop. Assuming the valid BLI values are in a dynamic range
named "BLI" in one column in a worksheet named "ValidEntries":
Dim iInput As Long
Dim vValid As Variant
Dim iValid As Long
iInput = Application.InputBox(Prompt:="Which BLI would you like to enter?",
Type:=1)
Do
vValid = Worksheets("ValidEntries").Range("BLI").Value
For iValid = LBound(vValid,1) To UBound(vValid,1)
If iInput = vValid(iValid, 1) Then Exit Do
Next
iInput = Application.InputBox(Prompt:="Which BLI would you like to enter?
Enter a valid one this time.", Type:=1)
Loop
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
"Mathew" wrote in message
...
Rick: Here are the numbers: 1406, 1408, 1410, 1411, 1415, 1430, 1440,
1450,
1460, 1465, 1470, 1475, 1485, 1490, 1492, 1495, 1499, 1501, 1502. I've
eliminate 3 others from the list by dealing with them elsewhere in the
macro.
I'm curious as to why these could have a bearing on how to approach the
code. By the way, thanks for the help!
"Rick Rothstein (MVP - VB)" wrote:
Can you show us the 22 allowable numbers? (It may have a bearing on how
to
approach the code.)
Rick
"Mathew" wrote in message
...
I have a very complicated spreadsheet that many different users are
filling
out. It is a complex spreadsheet that I cannot change. I've got a
macro
to
help them input data into this spreadsheet. I need a little help with
it.
How would you create an input box that checks to see if the data
entered
is
correct? Below is the code:
ActiveCell.Value = InputBox("Which BLI would you like to
enter?")
What is needed is to check the BLI number entered, which must be a 4
digit
number, i.e. to verify if it is long enough, and if it is one of the 22
allowable numbers. Normally, I'd use a data validation for the cell in
the
spreadsheet but the users are "getting lost" in the spreadsheet. Any
help
would be appreciated.