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]

marclarzen,

Here is a Worksheet_Change event for you to consider, based on your two flat text displays.

Sample worksheets:

Excel 2007
	A	B	C
1
2
3
4
Sheet2

And, in Sheet1, if we scan ab1234 into cell A1, you would see the text for a split second, and, then:

Excel 2007
	A	B
1
2
Sheet1

And, in Sheet2:

Excel 2007
	A	B	C
1	ab1234	08/26/2016 08:34:12 AM
2
3
4
Sheet2

Then you would scan in Sheet1, cell A1, the following text (separately):
ab2222
ab1234


And, in Sheet2 you would see:

Excel 2007
	A	B	C
1	ab1234	08/26/2016 08:34:12 AM
2	ab2222	08/26/2016 08:37:35 AM
3	ab1234	08/26/2016 08:37:43 AM
4
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/26/2016, ME960442
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim w2 As Worksheet, nr As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Set w2 = Sheets("Sheet2")
  With w2
     nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
     If nr = 2 And w2.Range("A1") = vbEmpty Then nr = 1
    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
  End With
  Target.Clear
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then add the text in Sheet1, cell A1.

 

[marclarzen]

Hi hiker,


I'll try this when i got home.
However, I'd like to clarify my inquiry:
User will always and only scan in one (1) cell which is Sheet1!A1. Therefore, the value in cell A1 will be changed every scan. But what I need is that even user scanned more barcode stickers, all data captured will be transferred to another worksheet (Sheet2). Please see below simulated image.


Barcode dummy:
Unit 1 = ab1234
Unit 2 = ad5678

1st Scan:	1st barcode sticker to scan is Unit 1 (ab1234)
Excel 2007
	A	B	C
1	ab1234
2
3
	Sheet 1
Excel 2007
	A	B	C
1	ab1234	8/26/2016 8:32:13 PM
2
3
	Sheet 2
2nd Scan:	after scanning Unit 2, data (ab1234) will be
		changed to ad5678 at cellA1 but will record in Sheet2
Excel 2007
	A	B	C
1	ad5678
2
3
	Sheet 1
Excel 2007
	A	B	C
1	ab1234	8/26/2016 8:32:13 PM
2	ad5678	8/26/2016 8:34:01 PM
3
	Sheet 2
3rd Scan:	after scanning same Unit 2, cellA1 will not be
		changed but will record new time stamp at Sheet2
Excel 2007
	A	B	C
1	ad5678
2
3
	Sheet 1
Excel 2007
	A	B	C
1	ab1234	8/26/2016 8:32:13 PM
2	ad5678	8/26/2016 8:34:01 PM	8/26/2016 8:34:45 PM
3
	Sheet 2

<tbody>
</tbody>

I apologize for the flat tables here but I hope this simulation helps you more in visualizing the required output.
Thank you in advance.

 

[hiker95]

marclarzen,

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/27/2016, ME960442, Version 2
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
  Target.Clear
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[marclarzen]

Hi Hiker,

Good day.
First of all, thank you very much for your support about this.
however, i have two (2) additional inquiries about it.
1. Upon scanning of barcode, I cannot see the barcode data at sheet1. I can only check it once I open the sheet2. Is it possible to see the barcode data at cell sheet1!A1 upon scanning and then at sheet2!A1 will also display this barcode?
2. Is it possible to restrict the scanning at cell A1(sheet1) only? If the user try to scan at cell A2 (sheet1), system will just put the data at A1.

Once again, thank you very much in advance.
I hope you'll able to help more people here.

Regards,
Marc

 

[marclarzen]

Hi hiker,

One more thing. The cell use for scanning is a merge cell from sheet1!A1:C11. The program said error because of the merge cell.

Thanks.

 

[hiker95]

The cell use for scanning is a merge cell from sheet1!A1:C11. The program said error because of the merge cell.

marclarzen,

I have no experience with the Worksheet_Change event with a merged cell, and, have not seen examples where this can be accomplished.

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]

marclarzen,

I have no experience with the Worksheet_Change event with a merged cell, and, have not seen examples where this can be accomplished.

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.

Thanks, Hiker. Anyway, I will just change the layout and break it down into cells. How about my last two (2) inquiries? is it possible?
Once again, thank you very much for your continuous assistance.

 

[hiker95]

marclarzen,

With the merged cells in Sheet1 range A1:C11 removed.

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 08/28/2016, ME960442, Version 3
' 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
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

 

[marclarzen]

Hi Sir Hiker,

Thank you very much for this code. it helped us a lot. however, one more thing. could you add the autosave function in this VBA code? once again thank you very much.