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

M

marclarzen

New Member
Joined
Aug 23, 2016
Messages
14
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
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ab1234
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ab1234
[/TD]
[TD]08/25/2016 04:39:28 PM
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


please help me.
thank you in advance
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
marclarzen,

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

Sample worksheets:


Excel 2007
ABC
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
AB
1
2
Sheet1


And, in Sheet2:


Excel 2007
ABC
1ab123408/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
ABC
1ab123408/26/2016 08:34:12 AM
2ab222208/26/2016 08:37:35 AM
3ab123408/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

Code: 
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.
 
Last edited:
Upvote 0
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



[TABLE="width: 447"]
<tbody>[TR]
[TD="colspan: 2"]1st Scan: [/TD]
[TD="colspan: 2"]1st barcode sticker to scan is Unit 1 (ab1234)[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Excel 2007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ab1234[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Excel 2007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ab1234[/TD]
[TD]8/26/2016 8:32:13 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]2nd Scan: [/TD]
[TD="colspan: 2"]after scanning Unit 2, data (ab1234) will be[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]changed to ad5678 at cellA1 but will record in Sheet2[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Excel 2007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ad5678[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Excel 2007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ab1234[/TD]
[TD]8/26/2016 8:32:13 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ad5678[/TD]
[TD]8/26/2016 8:34:01 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]3rd Scan: [/TD]
[TD="colspan: 2"]after scanning same Unit 2, cellA1 will not be [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]changed but will record new time stamp at Sheet2[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Excel 2007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ad5678[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Excel 2007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ab1234[/TD]
[TD]8/26/2016 8:32:13 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ad5678[/TD]
[TD]8/26/2016 8:34:01 PM[/TD]
[TD]8/26/2016 8:34:45 PM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I apologize for the flat tables here but I hope this simulation helps you more in visualizing the required output.
Thank you in advance.
 
Upvote 0
marclarzen,

Try the following:

Code: 
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
The cell use for scanning is a merge cell from sheet1!A1:C11. The program said error because of the merge cell.
Click to expand...

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.
 
Upvote 0
hiker95 said:
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.
Click to expand...


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.
 
Upvote 0
marclarzen,

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

Try the following:

Code: 
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Help with scanning barcodes into an excel invoice
2
Replies
12
Views
1K
RoryA
RoryA
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
388
maiwarits
M
S
  • Question Question
Scan Attendance
Replies
1
Views
465
Stewart11715
S
JenniferMurphy
  • Solved
Is there a way to format date-time with proper alignment?
Replies
8
Views
450
JenniferMurphy
JenniferMurphy
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
902
Insurance Dave
I

Forum statistics

Threads
1,223,532
Messages
6,172,878
Members
452,486
Latest member
standw01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top