Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box data validation
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box data validation
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. -- JNW "Mathew" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box data validation
JNW: Thanks alot! I'll give it a try!
"JNW" wrote: 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. -- JNW "Mathew" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box data validation
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box data validation
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box data validation
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box data validation
Jon: Thanks for the help! The code was very elegant in its approach and it
worked great! "Jon Peltier" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box data validation
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 "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DATA VALIDATION INPUT MESSAGE | Excel Discussion (Misc queries) | |||
Data Validation of user input | Excel Worksheet Functions | |||
Data Input Validation | Excel Discussion (Misc queries) | |||
Data Validation Input Message | Excel Discussion (Misc queries) | |||
Data Validation list in Input Box... Possible ? | Excel Programming |