I am trying to scan barcode and have Excel move to the right cell

Question

I need to make the cursor move to the right cell in Excel when I scan a bar code, but currently it goes down. I don't want to change it in the Excel Options because I need to have the cell cursor go down when I hit enter on other sheets.

You can do something like this using the `Workbook_SheetActivate` event http://excel.tips.net/T002073_Choosing_Direction_after_Enter_On_a_Workbook_Basis.html — Tim Williams, Apr 04 '16 at 17:12
It doesn't seem to work. I'm not sure if when I input the code I'm not hitting the right thing to save it or make it effective. I'm just hitting the save button. When I close out of VBA and go back in it's still there. — anra, Apr 04 '16 at 17:42
You will have to adapt that code for your needs - unlikely it will work exactly as posted... — Tim Williams, Apr 04 '16 at 17:44
Your scanner can probably be configured to append a `tab` instead of `enter` to your data — Enrico, Apr 05 '16 at 13:14
This answer provides information about the complexities of handling control characters such as HT and CR: http://stackoverflow.com/a/30904203/2568535 — Terry Burton, Apr 05 '16 at 16:40

score 1 · Answer 1 · answered Aug 24 '16 at 15:40

one solution I found is this block of code I initially started with.

Open a ActiveX textbox from developer under insert and place it in the sheet you wish to scan into. Select A1, then click the textbox. Make sure the design mode view is off in developer (look at the toolbar ribbon).

The line with ** around it is what you need to change. This is set to length, but you could also play around with other things like Cases. Try making a barcode with 12345 as the value.

Private Sub TextBox1_Change()
**If Len(TextBox1.Value) = 5 Then**
    ActiveCell.Value = TextBox1.Value
    ActiveCell.Offset(1).Activate
    Application.EnableEvents = False
    TextBox1.Activate
    TextBox1.Value = ""
End If

End Sub

Example of a case. Try making a barcode with "Waste R - 1" as the value.

Private Sub TextBox1_Change()

Dim ws As Worksheet, v, k, i, j
Set ws = Worksheets("Sheet1")

v = TextBox1.Value
k = 0
i = 0
j = 0

Select Case v

Case "Waste R - 1": i = 2
k = 1
j = "Waste R - 1"
'Start i at whatever column you want to start at
'k is the type of case and if statement you want to run (I have several, but for simplicity, I have only attached one)


End Select

    If k = 1 Then

    ws.Cells(1, 1) = ws.Cells(1, 1).Value + 1
    ' adds number into cell (A1) to reference which column to be in
    ' Starts in Column A then adds a value of one to reference column B

    i = ws.Cells(1, 1)
    'Sets i = to the A1 value

    Cells(1, i).Value = j
    ' You may be able to set this to textbox1.value
    ' Says to put the Textbox Value into whatever column and row A

    TextBox1.Activate
    TextBox1.Value = ""
End If
End Sub

Make sure to put a value of 2 in cell A1 to start.

You could turn this into a loop if you wanted to eventually. I am sure there are easier ways, but I made an excel template to paste into the VBA sheet one module reference. It is very easy to update. Let me know if i can help with any other barcode questions

I am trying to scan barcode and have Excel move to the right cell

1 Answers1