Scan barcode to excel with date & time stamp in & out in another worksheet.

[marclarzen]

Scanbarcode to excel with date & timestamp in & out.
#10

I followed the thread of this topic and found it very useful for my requirement. however, there is a bit difference.
I need the records/date and time stamp to be recorded in another worksheet.

user will keep on scanning the barcode in a fix cell (Sheet1!A1), but the record will be stored in another worksheet like below sample data image

Sheet1

	A	B	C
1	ab1234
2
3

<tbody>
</tbody>

Sheet2

	A	B	C
1	ab1234	08/25/2016 04:39:28 PM
2
3

<tbody>
</tbody>

please help me.
thank you in advance

 

[hiker95]

Thank you very much for this code. it helped us a lot.

marclarzen,

Thanks for the feedback.

You are very welcome. Glad I could help again.

one more thing. could you add the autosave function in this VBA code?

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 09/07/2016, ME960442, Version 4
' The following macro code should ONLY BE IN Sheet1
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim w2 As Worksheet, bcr As Range, nr As Long, nc As Long, n As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Set w2 = Sheets("Sheet2")
  With w2
    n = Application.CountIf(w2.Columns(1), Target.Value)
    If n = 0 Then
      nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And w2.Range("A1") = vbEmpty Then
        nr = 1
      End If
      w2.Range("A" & nr) = Target.Value
      w2.Range("B" & nr) = Now()
      w2.Range("B" & nr).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
      w2.Columns("A:B").AutoFit
    ElseIf n > 0 Then
      nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
      If w2.Cells(nr, 1).Value = Target.Value Then
        nc = w2.Cells(1, Columns.Count).End(xlToLeft).Column + 1
        w2.Cells(1, nc) = Now()
        w2.Cells(1, nc).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
        w2.Columns(nc).AutoFit
      Else
        Set bcr = w2.Columns(1).Find(Target.Value, LookAt:=xlWhole)
        nc = w2.Cells(bcr.Row, Columns.Count).End(xlToLeft).Column + 1
        w2.Cells(bcr.Row, nc) = Now()
        w2.Cells(bcr.Row, nc).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
        w2.Columns(nc).AutoFit
      End If
    End If
  End With
  ActiveWorkbook.Save
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[marclarzen]

Hi Hiker,

Good morning.
How are you? i hope you are fine.

I would like to inquire again regarding restricting the selection of cell in a1. The scenario will be: A1 is the scanning cell, every scan it will goes back to cell A1. Is it possible?
Thanks

 

[hiker95]

I would like to inquire again regarding restricting the selection of cell in a1. The scenario will be: A1 is the scanning cell, every scan it will goes back to cell A1. Is it possible?

marclarzen,

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 10/25/2016, ME960442, Version 5
' The following macro code should ONLY BE IN Sheet1
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim w2 As Worksheet, bcr As Range, nr As Long, nc As Long, n As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Set w2 = Sheets("Sheet2")
  With w2
    n = Application.CountIf(w2.Columns(1), Target.Value)
    If n = 0 Then
      nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And w2.Range("A1") = vbEmpty Then
        nr = 1
      End If
      w2.Range("A" & nr) = Target.Value
      w2.Range("B" & nr) = Now()
      w2.Range("B" & nr).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
      w2.Columns("A:B").AutoFit
    ElseIf n > 0 Then
      nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
      If w2.Cells(nr, 1).Value = Target.Value Then
        nc = w2.Cells(1, Columns.Count).End(xlToLeft).Column + 1
        w2.Cells(1, nc) = Now()
        w2.Cells(1, nc).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
        w2.Columns(nc).AutoFit
      Else
        Set bcr = w2.Columns(1).Find(Target.Value, LookAt:=xlWhole)
        nc = w2.Cells(bcr.Row, Columns.Count).End(xlToLeft).Column + 1
        w2.Cells(bcr.Row, nc) = Now()
        w2.Cells(bcr.Row, nc).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
        w2.Columns(nc).AutoFit
      End If
    End If
  End With
  With Sheets("Sheet1").Range("A1")
    .ClearContents
    .Select
  End With
  ActiveWorkbook.Save
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[marclarzen]

Hi Hiker,

Good day.
First of all thank you for your immediate feedback. It works however, once other cell was selected it does not go back to cell A1.
example scenario:
cell A1 = BARCODING CELL
user unintentionally selected cell B3 then scanned the barcode sticker, data captured must be placed in cell A1 even the selected cell is at cell B3.
Is it possible?
Thank you in advance.

 

[hiker95]

Hi Hiker,

Good day.
First of all thank you for your immediate feedback. It works however, once other cell was selected it does not go back to cell A1.
example scenario:
cell A1 = BARCODING CELL
user unintentionally selected cell B3 then scanned the barcode sticker, data captured must be placed in cell A1 even the selected cell is at cell B3.
Is it possible?
Thank you in advance.

marclarzen,

In the following Worksheet_Change Event, if the user attempts to scan into Sheet1, into a cell other then cell A1, they will get a message box that will display:

You can only scan Barcodes into Sheet1 range A1

Then, the cell that they scanned into, or, added anything into, will be cleared, and, then the cursor will select Sheet1 cell A1.


Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 10/26/2016, ME960442, Version 6
' The following macro code should ONLY BE IN Sheet1
If Target <> Range("A1") Then
  MsgBox ("You can only scan Barcodes into Sheet1 range A1")
  ActiveCell.ClearContents
  Range("A1").Select
End If
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim w2 As Worksheet, bcr As Range, nr As Long, nc As Long, n As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Set w2 = Sheets("Sheet2")
  With w2
    n = Application.CountIf(w2.Columns(1), Target.Value)
    If n = 0 Then
      nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
      If nr = 2 And w2.Range("A1") = vbEmpty Then
        nr = 1
      End If
      w2.Range("A" & nr) = Target.Value
      w2.Range("B" & nr) = Now()
      w2.Range("B" & nr).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
      w2.Columns("A:B").AutoFit
    ElseIf n > 0 Then
      nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
      If w2.Cells(nr, 1).Value = Target.Value Then
        nc = w2.Cells(1, Columns.Count).End(xlToLeft).Column + 1
        w2.Cells(1, nc) = Now()
        w2.Cells(1, nc).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
        w2.Columns(nc).AutoFit
      Else
        Set bcr = w2.Columns(1).Find(Target.Value, LookAt:=xlWhole)
        nc = w2.Cells(bcr.Row, Columns.Count).End(xlToLeft).Column + 1
        w2.Cells(bcr.Row, nc) = Now()
        w2.Cells(bcr.Row, nc).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
        w2.Columns(nc).AutoFit
      End If
    End If
  End With
  With Sheets("Sheet1").Range("A1")
    .ClearContents
    .Select
  End With
  ActiveWorkbook.Save
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[marclarzen]

Hi Hiker,

Thank you very much, however, it won't work on a locked cells. The scenario is that excel has only one unlocked cell to prevent user in entering other data. However, in order to continue the barcode scanning, i inquire this solution.

Once again, thank you.

marc

 

[hiker95]

it won't work on a locked cells.

marclarzen,

This is the first that you have mentioned anything concerning locked cells.

I do not understand what you are after.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[marclarzen]

Bump

 

[marclarzen]

Hi Hiker,

Sorry, i was not able to tell you that i restrict the worksheet by protecting the sheet (locking the cells) except for A1. This is to prevent the user to input/key-in other information on the cell excep in cell A1. However, every barcode scanning, from cell A1 it moves down so user will keep on selecting cell A1. I hope this clarify my inquiry.

Thank you very much.
Marc

 

[chrischinwah]

Hello, I was using the code above, and noticed that If I were to scan the same barcode again, it would enter the date/time into the next cell across. I was wondering on how the code can be edited to have the information, be entered below.