Inventory Management using Barcode Scanner

[mdugan2]

Hello,

I am looking for some code that would allow me to use a bar-code scanner to create a simple inventory. I work in an office with a high degree of check-in/check-out activity for inventory, and being able to simply scan items in/out would be a big help.

Consolidating that data has been more of a challenge than I anticipated. My initial idea is that the sheet would look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item Name[/TD]
[TD]User[/TD]
[TD]Dispatcher[/TD]
[TD]Check Out[/TD]
[TD]Check In[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]HOTSPOT1[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 113"]
<tbody>[TR="class: grid"]
[TD="width: 113, align: right"]4/7/2015 16:18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TABLET1[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 113"]
<tbody>[TR="class: grid"]
[TD="width: 113, align: right"]4/7/2015 16:18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR="class: grid"]
[TD="width: 113, align: right"]4/7/2015 16:25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Battery reported low[/TD]
[/TR]
[TR]
[TD]TABLET1[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 113"]
<tbody>[TR="class: grid"]
[TD="width: 113, align: right"]4/7/2015 16:27[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]New battery swapped[/TD]
[/TR]
</tbody>[/TABLE]

The "User", "Dispatcher", and "Notes" column would be entered manually. Ideally, the "Item Name" and the out/in times would come from the bar-code scan. I have already created Code39 bar-codes with the Item names.

I have been using the following thread for guidance, but was having one big issue with it's format. Rather than have the Check In / Check Out time continually log in the same row, I would prefer to have the scan enter a new row after it has already been returned. I hope I've demonstrated this above. Here is the thread I am referencing. There is, of course, a large degree of flexibility in the format of this sheet. As long as all of the information is able to be entered and the format is readable, I'm all ears!

As I have very limited coding skills, any insight would be appreciated. Thank you so much for your help!

 

[mdugan2]

A few more relevant pieces of information.

Platform: Excel 2010

The below code is what I have been working with currently h/t to hiker95:

[COLOR=#333333]Option Explicit[/COLOR]Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/30/2012
' http://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html
If Intersect(Target, Range("A2:A3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim lc As Long, fr As Long, n As Long, nr As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  n = Application.CountIf(Columns(1), Cells(Target.Row, 1))
  If n = 1 Then
    lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    If lc = 1 Then
      Cells(Target.Row, lc + 2) = Format(Now, "m/d/yyyy h:mm")
    ElseIf lc > 2 Then
      Cells(Target.Row, lc + 1) = Format(Now, "m/d/yyyy h:mm")
    End If
  Else
    fr = 0
    On Error Resume Next
    fr = Application.Match(Cells(Target.Row, 1), Columns(1), 0)
    On Error GoTo 0
    If fr > 0 Then
      lc = Cells(fr, Columns.Count).End(xlToLeft).Column
      Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
      Target.ClearContents
    End If
  End If
  On Error Resume Next
  Me.Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  nr = Me.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  Me.Cells(nr, 1).Select
  .EnableEvents = True
  .ScreenUpdating = True
End With [COLOR=#333333]End Sub[/COLOR]