Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've been asked by our warehouse guy to help him with an excel spreadsheet to increase his productivity. The problem is, I'm not what you would call an "excel expert". What he wants sounds very simple, I'm just not sure how to do it ...so here goes.

Right now he has a usb barcode scanner. When he scans to an excel spreadsheet the default action after a scan is that it moves down one cell. So, if it's in cell A1, it moves down to B1. What he needs it to do is move in this sequence when he scans: A1,A2,B1,B2,C1,C2,D1,D2 etc all the way down the page. The reason for this is he needs the part and lot number right next to each other. If someone could help me with this I would be eternally grateful. Thanks!

share|improve this question
    
Is there software installed for the scanner? You could try to adjust the settings. –  Robert Jan 16 '12 at 15:30
    
It's a basic USB scanner. There really isn't a lot of configuration or software. I was thinking this would be more in the format of the cell for an excel spreadsheet template. If someone could help me build a template I could really really appreciate it. Thanks! –  Joe B Jan 16 '12 at 15:37

4 Answers 4

up vote 6 down vote accepted

Select columns A and B

  1. Hit CTRL and 1

  2. Click on Protection

  3. Untick "Locked"

Not sure what version of Excel you have, tags for 2007 and 2010 are in the post, but then:

Protect the sheet, allowing selection of Unlocked Cells Only, should be under the format menu on the Home tab of the ribbon.

Set Excel to move right on Enter, it's under Excel Options, Advanced in Excel 2007 and above.

share|improve this answer
    
This is a great answer! The "enter" or carriage return is either programmed in to the scanner or part of the printed bar code. In either case, changing that won't solve the users problem. Paperlantern's deep knowledge of Excel yields an elegant solution. +1! –  Dave Becker Jan 16 '12 at 16:35
    
This worked perfectly! Thank you so much! –  Joe B Jan 16 '12 at 18:33

Many of these barcode scanners can be programmed (typically by scanning special barcodes provided in a manual for that purpose) to end the barcode with TAB instead of CR (Enter).

You still need to lock cells that are not to be entered.

enter image description here

(Note: these are proprietary and specific to a particular make (and maybe model))

share|improve this answer

I know this is old but someone may still want an answer:

Further, and more specific to the question asked; you could add code to the sheet's Worksheet_Change macro:

  • Right click the sheet and do view code.
  • Change the dropdown (General) to Worksheet and (Declarations) to Worksheet_Change.

Before I give the code to do what you want, note that if you have an earlier than Excel 2007, your user will only have 256 columns (A-IV) to work with. After Excel 2007 he will have 16,384 columns.

First, if all he wanted to do is move to the next column, you could just go to Options/Advanced and change DOWN to RIGHT for cursor movement on ENTER. But since he desires a custom setting I provide this code.

Simply paste it into the white space under the Worksheet and Change dropdowns.

Private Sub Worksheet_Change(ByVal Target As Range)
    tgr = Target.Row
    tgc = Target.Column
    If Trim(Cells(1, tgc)) <> "" And Trim(Cells(2, tgc)) <> "" Then
        Cells(1, tgc + 1).Select
    ElseIf Trim(Cells(1, tgc)) <> "" And Trim(Cells(2, tgc)) = "" Then
        Cells(2, tgc).Select
    End If
End Sub

This code assumes the user is starting a cell A1.

share|improve this answer

The barcode scanner should be able to be programmed to "Tab" over to the next cell after each scan.

Find out the make and model of the scanner and go to their website and they should have instructions on how to do this. Typically it involves scanning a single or sequence of barcodes that programs this function into the scanner.

This is what I had to do for the different scanners that we were using for physical inventory at my company.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.