Barcode scanning with Excel

[Chief1904]

My office is trying to go paperless with our hunting program and issue ID cards with bar codes. I'm trying to have a barcode scanner be able to scan the ID card and pull up their information in excel or access. How can I do this? My boss doesn't want to spend $12,000 on a full setup of software and hardware. Any help would be great!

 

[zmunns]

I think I understand. Although the two are separate inputs, every time a "scan" takes place, it should record both data points, the A2 cell copying to the Inventory Scan and the B2 cell copying to the Location Scan. I think the disconnect is in the Inventory Status. In this sheet, the output should render Style # 3/ Shirt/ L/ Receiving/ Date-time

For the second portion, what I'm trying to accomplish is after any scan, either in A2 or B2, the scan will go back to A2 as the active cell to avoid manually redirecting it every scan.

Thanks

Zach

 

[L. Howard]

Although the two are separate inputs, every time a "scan" takes place, it should record both data points, the A2 cell copying to the Inventory Scan and the B2 cell copying to the Location Scan.

In that case, I will need to take cell B2 off the event_code activation. That is to say, both A2 and B2 will need to have correct and associated data in place before the code runs. As the code is written now, if you wanted to scan in two brand new barcodes to be processed, as soon as you scan in either A2 or B2 the code will run. So you can see if there was an "old" barcode in B2 and you scanned a "new" barcode into A2, the code would run using the new barcode (A2) and the old (B2) which would be erroneous.

Previously, you said you wanted each scan, A2 or B2, to be individual. That does not seem to be possible if both data points are to be process together.

Howard

 

[zmunns]

Essentially how it would be set up in a perfect world is someone would "check in" to a location, say receiving. Then they would "check out" of their location once they've completed scanning the inventory they were assigned. In this case, the B2 scan would bind the associated inventory scans between the "check in" and "check out" events and the Inventory Status would show all the scans from the inventory and the location (receiving) at which they were scanned.

Hope this makes sense. Sorry for the confusion and thanks for your patience and help

 

[L. Howard]

Essentially how it would be set up in a perfect world is someone would "check in" to a location, say receiving. Then they would "check out" of their location once they've completed scanning the inventory they were assigned. In this case, the B2 scan would bind the associated inventory scans between the "check in" and "check out" events and the Inventory Status would show all the scans from the inventory and the location (receiving) at which they were scanned.

I have no idea how to translate that scenario to a worksheet, involving two scan cells that must be calculated together.

Maybe this revision will be a step in the right direction. Completely delete the previous Change_Event code in the sheet Scan Outputs module and copy this one into it. Fires off cell A2 only.

Requires the user to scan into B2 first and then scan into A2 second.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$A$2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim LRow As Long, LRowX As Long
Dim aRng As Range, rngFnd As Range, rngFndX As Range
Dim myFnd As String, myFndX As String

myFnd = Target
myFndX = Target.Offset(, 1)

'MsgBox myFnd & " " & myFndX

  If myFnd = "" Then
    Exit Sub
  ElseIf IsNumeric(myFnd) Then
    myFnd = Val(myFnd) '/ converts a "text" number to a value
  Else
    '/ is text and that is okay
End If

   With Sheets("Data Input")
    LRow = Sheets("Data Input").Cells(Rows.Count, "A").End(xlUp).Row
   
    Set rngFnd = Sheets("Data Input").Range("A2:A" & LRow).Find(What:=myFnd, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)
    If Not rngFnd Is Nothing Then
  
       rngFnd.Resize(1, 5).Copy Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp)(2)
       Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 5) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
       
       rngFnd.Offset(, 1).Resize(1, 3).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp)(2)
       Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 4) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
       
      Else

        MsgBox "No " & myFnd & " match found."
           
      End If
      
      
           LRowX = Sheets("Data Input").Cells(Rows.Count, "H").End(xlUp).Row
   
      Set rngFndX = Sheets("Data Input").Range("H3:H" & LRowX).Find(What:=myFndX, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)

   If Not rngFndX Is Nothing Then
    
         rngFndX.Resize(1, 2).Copy Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp)(2)
         rngFndX.Offset(, 1).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 3)
         Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 2) = Format(Now(), "yyyy-MM-dd hh:mm:ss")

       Else

        MsgBox "No " & myFndX & "match found."
 
      End If
    End With
  
[A2].Activate
End Sub

 

[zmunns]

This seems to work exactly as I need it! I really appreciate your help and can't thank you enough!

 

[L. Howard]

You're welcome, glad it is working for you.

Just to add, you could use a formula to do the same as this line in the code.

rngFndX.Offset(, 1).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 3)

It supplies the Location Scanned (column D) on sheet Inventory Status. The one that was returning the wrong location.

You would find the line as shown above and replace or comment it out and copy this into its place.

       With Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 3)
         .Formula = "=VLOOKUP('Scan Outputs'!$B$2,'Data Input'!$H$3:$I$" & LRowX & ",2,0)"
         .Value = .Value
       End With

It uses a formula and returns ONLY the value of the formula to the cell. There will be no formula in the cell on the sheet.
I see no great advantage of one over the other, but thought I would offer it up.
It uses the same data range on sheet Data Input as it's source.

Howard

 

[zmunns]

Thanks. I will test both and see which one functions better in the field. Do you have any ideas for returning the active cell to A2 after every scan? That is the last piece I'm working on

Thanks

 

[L. Howard]

Do you have any ideas for returning the active cell to A2 after every scan? That is the last piece I'm working on

Thanks

On my Scan Outputs test sheet where I enter a value in A2 and hit enter, the macro runs and cell A2 is activated (re-selected).
Look at the very bottom of the code and you will see the A2 activate line. This should do a return to A2. Does it NOT do that?

        Else

        MsgBox "No " & myFndX & "match found."
 
      End If
    End With
  
[A2].Activate
End Sub

Howard

 

[zmunns]

It does. Sorry I must've missed that the first time around. Thanks for all your help Howard. Couldn't have done it without you

 

[zmunns]

Hey Howard

I have one more thing. There have been a couple additional fields added to the data set which have been attached below. I've tried rearranging the code to facilitate the new outputs but wasn't successful. Could you help make this minor adjustment? Simply speaking, the Data Input table has grown. The needed output columns are A-F and the Location was pushed to column N. If you can help make the A-F column data render in the Inventory Status worksheet and move the location scan to N, it would be greatly appreciated

https://drive.google.com/file/d/1EwAamGINup1PH0vY9FaLRBLn-2Q37U4S/view?usp=sharing

Thanks much

Zach