CODEWITHSUNDEEP
excelvbabarcodebarcode-scannerenter
anra
anra

Reputation: 3

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

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.

Upvotes: 0

Views: 3837

Answers (1)

Seanm
Seanm

Reputation: 23

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

Upvotes: 1

Related Questions