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
 
Sort by date Sort by votes
Thank you very much for this code. it helped us a lot.
Click to expand...

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?
Click to expand...

Try the following:

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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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?
Click to expand...

marclarzen,

Try the following:

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


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:

Code: 
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
 
Last edited:
Upvote 0
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
 
Upvote 0
it won't work on a locked cells.
Click to expand...

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