Help with scanning bar codes, and populating a different field

[Ryan2286]

I am a records management auditor, I recently switched to using Excel from other programs and I am hoping there is a way to make my life easier,

What I want to do is scan a barcode with a barcode scanner and have it find the barcode in my Excel workbook, and then populate a cell a few columns over. e.g. I scan barcode, excel finds it in A51 and updates G51 with "7D2". Then it would be nice if I could continue to do that by just pushing the button on my scanner, though if I have to press something like "ctrl-f" that wouldn't be the end of the world either!

Thanks for your help!

 

[L. Howard]

Hi Ryan,

I don't have a scanner but it seems some produce a worksheet event and some do not.
Copy and paste this in the worksheet vb editor and do a single scan.

Post back with results if you got message or did not get message. If you scan and do not get message enter anything in a cell, ie. xxx, from the keyboard and hit enter. If you still don't get the message with a common entry, the code is missplaced and we will need to fix that.

Once we figure that out, then we need to know where "7D2" is or will it always be "7D2" etc. that will populate G51. Stuff like that.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Scanned code produces a worksheet_change event", vbOKOnly, "Scan Checker"
End Sub

Regards,
Howard

 

[Ryan2286]

Thank you for helping!

I'm going to list the steps I made, as I am fairly new to Excel and you may find something wrong with what I am doing.

Open my workbook>Alt F11>in my project window I click "VBAProject (recordsworkbook.xlsx)>insert>module>Paste your code>close.

From there I don't really know what to do, I went into Excel and used my barcode scanner in a random cell and it just showed the number of the barcode that I scanned in the cell.

I also went to View>macros and there was nothing there... Not sure if there should be or not?

I apologize if I am missing something, but I am very new to Excel as far as using VB and macros.

 

[L. Howard]

You are close, you inserted the code in an Insert > Module with a name similar to this, way up at the top... Book1 - [Module1(Code)]

Do alt + F11 (or right click the sheet tab name you want the code to work in and click on View Code) Paste the code there in the big white space.

Now go to the sheet and scan something in to see if you get the message. I'm hoping the scan creates a worksheet change event. If it does then that will eliminate the need for you to mannually produce a change event after each scan.

I'll be here all day so post back as soon as you are able.

Regards,
Howard

 

[Ryan2286]

Hey Howard,

Thank you so much for helping me! I asked one of my co-workers to help me out and he's been working on it all morning. He wrote this program and it seems to work pretty well. I am able to scan the barcode in K4 and then choose what I want it to populate the corresponding cell with in K7. This is exactly what I was looking for, though if I was able to continue scanning rather than having to press the macro button every time that would be ideal, also would be nice not to have to highlight K4 in order to scan another barcode. Not sure if this is possible though.

Public Sub checkBarCodeValue()
Dim LastCell As RangeDim LastCellNum As IntegerDim DBCode As StringDim DBNum As LongDim UpdateValue As StringDim CodeNum As LongDim UpdateRow As IntegerDim SuccessFlag As Integer
Sheets("Sheet1").ActivateRange("B2").Activate
SuccessFlag = 0
If Range("K4").Value = "" Then
Else
CodeNum = Range("K4").ValueUpdateValue = Range("K7").Value
Set LastCell = ActiveCell.End(xlDown).Offset(-1, 0)
LastCellNum = LastCell.Row
For i = 2 To LastCellNumDBCode = Range("B" & i).Value
If DBCode = "" Then
Else
DBNum = CLng(DBCode)
If DBNum = CodeNum Then
Range("H" & i).Value = UpdateValueSuccessFlag = 1
ElseIf i = LastCellNum And SuccessFlag = 0 Then
MsgBox ("A Matching BarCode Was Not Found!  Please Try Again")
End If
End If
Next i
End If
End Sub

 

[L. Howard]

Try this.

May have to put something like this in so the code doesn't run if you do something in an unrelated cell somewhere else on the sheet.
If Target <> range("K4") then exit sub

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim LastCell As RangeDim LastCellNum As IntegerDim DBCode As StringDim DBNum As LongDim UpdateValue As StringDim CodeNum As LongDim UpdateRow As  IntegerDim SuccessFlag As Integer
Sheets("Sheet1").ActivateRange("B2").Activate
SuccessFlag = 0
If Range("K4").Value = "" Then
Else
CodeNum = Range("K4").ValueUpdateValue = Range("K7").Value
Set LastCell = ActiveCell.End(xlDown).Offset(-1, 0)
LastCellNum = LastCell.Row
For i = 2 To LastCellNumDBCode = Range("B" & i).Value
If DBCode = "" Then
Else
DBNum = CLng(DBCode)
If DBNum = CodeNum Then
Range("H" & i).Value = UpdateValueSuccessFlag = 1
ElseIf i = LastCellNum And SuccessFlag = 0 Then
MsgBox ("A Matching BarCode Was Not Found!  Please Try Again")
End If
End If
Next i
End If
Range("K4").Select
End Sub

Howard