Input Box data validation

[Mathew]

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.

 

[JNW]

Try this. (You'll need to have your valid list of allowable numbers on a
sheet somewhere or modify the code to have it look at an array)


Sub getinfo()
Dim res As Variant
ActiveCell.Value = InputBox("Which BLI would you like to enter?")
res = Application.Match(ActiveCell.Value, Range("b1:b5"), 0)
'change above range to match the location of your
'valid list of allowable numbers
If Len(ActiveCell.Value) <> 4 Then
GoTo NotCorrect
End If
If IsError(res) Then
GoTo NotCorrect
End If
Exit Sub

NotCorrect:
MsgBox "try again"
getinfo 'may need to change this based on how the inputbox is called
End Sub

One problem that could occur is you could run out of stack space if the user
enters in a wrong value too many times. One way you could correct this is
make the msgbox that says try again be more meaningful by including valid
entries.
i.e.
msgbox "you must use one of the following codes:" & _
vbcrlf & vbcrlf & "1111" & vbcrlf & "2222"

etc.

 

[Mathew]

JNW: Thanks alot! I'll give it a try!

Try this. (You'll need to have your valid list of allowable numbers on a
sheet somewhere or modify the code to have it look at an array)


Sub getinfo()
Dim res As Variant
ActiveCell.Value = InputBox("Which BLI would you like to enter?")
res = Application.Match(ActiveCell.Value, Range("b1:b5"), 0)
'change above range to match the location of your
'valid list of allowable numbers
If Len(ActiveCell.Value) <> 4 Then
GoTo NotCorrect
End If
If IsError(res) Then
GoTo NotCorrect
End If
Exit Sub

NotCorrect:
MsgBox "try again"
getinfo 'may need to change this based on how the inputbox is called
End Sub

One problem that could occur is you could run out of stack space if the user
enters in a wrong value too many times. One way you could correct this is
make the msgbox that says try again be more meaningful by including valid
entries.
i.e.
msgbox "you must use one of the following codes:" & _
vbcrlf & vbcrlf & "1111" & vbcrlf & "2222"

etc.

 

[Rick Rothstein \(MVP - VB\)]

Can you show us the 22 allowable numbers? (It may have a bearing on how to
approach the code.)

Rick

 

[Mathew]

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!

 

[Jon Peltier]

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

 

[Mathew]

Jon: Thanks for the help! The code was very elegant in its approach and it
worked great!

 

[Rick Rothstein \(MVP - VB\)]

If there were a mathematical "pattern" of some sort, then perhaps the Like
operator or a mathematical equation could be used to test the validity of
the entered number. However, I don't see such a pattern, so I probably would
test the validity of the user's entry like this...

Dim BLI As String
Dim ValidNumbers As String
ValidNumbers = "*1406*1408*1410*1411*1415*1430*1440*" & _
"1450*1460*1465*1470*1475*1485*1490*" & _
"1492*1495*1499*1501*1502*"
..........
..........
..........
Do
BLI = InputBox("Which BLI would you like to enter?")
Loop Until InStr(ValidNumbers, "*" & BLI & "*") Or BLI = ""
..........
..........

I broke up the ValidNumbers assignment in convenient places to stop you
newsreader from possibly breaking it up haphazardly. You can recombine the
three continued lines into a single line statement if you so wish (just make
sure there is an asterisk between every numbers). Note that the user must
enter either one of your numbers **OR** an empty string (assuming you want
to leave the user with an escape mechanism; perhaps they have the wrong
number and need to get out the validity checking loop). Because the empty
string is a possibility, you will probably need to add code to handle that
situation.

Rick