Then in E2, you use the INDIRECT function to get Excel to realize that what you want from D2 a named range.
For the validation list, you reference this list directly (=equipment). The INDIRECT function, in this type of situation recognizes that and uses the named range rather than what is directly in the cell.įirst (in D2) you need to name a range that contains all of the names of your other lists. In your case, you want to put a named range (or a list) into cell A1. For example, if you referenced A1 and A1 had VLV in it, then it would only allow entry of "VLV". Now you'll only be able to go through the locked cells and hopefully the scanner will work correctly as well.Normally when you reference a cell in a Validation list, it uses what's in that cell to set it's validation. NOTE: if locked wasn't turned on, then you will need to select the entire sheet, turn it on, then repeat this entire process. Right-click one of the highlighted cells you want the numbers to go in. The second method would be to create a VBA macro. You might be at the whim of the scanner software, so this may not work. If you turn off locked on cells outside of the column, then those cells could potentially be selected. Keep in mind, that this will basically lock out any other cell from being edited. The first method would be to use sheet protection to control what cells get selected, which I'll outline below. I'm not sure how the scanner is inserting the values, however, there are two possible methods. On the project window, doubleclick the "sheet" that you are using to input the barcodes On the project window, click the "+" beside the "Microsoft Excel Objects" On the project window, click the "+" beside the "VBA Project (bookname)" On the menu, click "View" then "Project Explorer" Here are the steps to put the code into the sheet:įirst and most important, make a backup of the workbook just in case. If Target.Row > 1 And Target.Column = 1 ThenĬells(Target.Row + 1, Target.Column).Select Private Sub Worksheet_Change(ByVal Target As Excel.Range) If you decide you don't like this, it is easily deleted.
You won't be able to use TAB under some conditions. It will take some time to get used to the way this works. Set the column to the barcode input data column by changing the "Target.Column = 1" to for example "Target.Column = 4" which will use column D. Put this code behind the sheet you are using for input data. There is a way to set up Excel to accomodate that will work 100% of the time. The problem is with the bar code you are scanning, not with Excel.
0 kommentar(er)
