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

Q

qpywsqp

New Member
Joined
Dec 2, 2012
Messages
26
Hi all. I have research around the internet and finally found out Macro able to solve my issue. But im totally not a programmer. Hopefully someone can help me on this.

I will scan QR Code with a scanner to cell A1 and wanted cell C1 to have date and time stamp for IN. When Scanner scan the same QR Code, D1 will have date and time stamp for out. If repeat third time scanning the same QR code after a few scanning, it will appear on next row. Which means A8 with the code and C8 with date and time stamp for IN again.

Is that possible to done with Macro?
 
Sort by date Sort by votes
Alright, that is not something i am looking.

Delegate 1 came and scanned, cell A2 will display Delegate unique number and cell C2 will time stamp Time In.
Delegate 2 came and scanned, cell A3 will display Delegate unique number and cell C3 will time stamp Time In.
Delegate 3 came and scanned, cell A4 will display Delegate unique number and cell C4 will time stamp Time In.
Delegate 4 came and scanned, cell A5 will display Delegate unique number and cell C5 will time stamp Time In.
Delegate 5 came and scanned, cell A6 will display Delegate unique number and cell C6 will time stamp Time In.
Delegate 6 came and scanned, cell A7 will display Delegate unique number and cell C7 will time stamp Time In.

And now my Active Cell should be at cell A8 awaiting next scan. At this time, Delegate 1 came out from the workshop and i should scan him/her for Time Out. By doing time stamp him/her for Time Out, i do not want to select the cell of Delegate 1 manually in earlier in-order to time stamp for Time Out. As it because from a big event i will not able to recognize every one of them.

So is that possible for macro to do this:
When Active Cell at cell A8 and i scan Delegate 1, automatic Macro will capture Time Out on D2 ofDelegate 1? Same goes to the rest? As because every delegate had a unique code.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
qpywsqp,

Your latest request/description is a lot more complicated especially working with the Worksheet_Change Event.

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


When you scan a delegate into the worksheet does your scanner routine then make the next blank cell in column A the active cell?


Sample raw data after five delegates have been entered (and the active cell is cell A7):


Excel Workbook
ABCDEF
1NameTime InTime OutTime InTime Out
2Jeffrey12/17/2012 15:25
3Steve12/17/2012 15:26
4Jacky12/17/2012 15:27
5Penny12/17/2012 15:28
6Cazz12/17/2012 15:29
7
8
Sheet1





If we scan in delegate Jeffrey into cell A7, we get this:


Excel Workbook
ABCDEF
2Jeffrey12/17/2012 15:2512/17/2012 15:38
3Steve12/17/2012 15:26
4Jacky12/17/2012 15:27
5Penny12/17/2012 15:28
6Cazz12/17/2012 15:29
7
8
Sheet1





If we scan in delegate Jeffrey again into cell A7, we get this:


Excel Workbook
ABCDEF
1NameTime InTime OutTime InTime Out
2Jeffrey12/17/2012 15:2512/17/2012 15:3812/17/2012 15:39
3Steve12/17/2012 15:26
4Jacky12/17/2012 15:27
5Penny12/17/2012 15:28
6Cazz12/17/2012 15:29
7
8
Sheet1





If we scan in a new delegate we get this:


Excel Workbook
ABCDEF
1NameTime InTime OutTime InTime Out
2Jeffrey12/17/2012 15:2512/17/2012 15:3812/17/2012 15:39
3Steve12/17/2012 15:26
4Jacky12/17/2012 15:27
5Penny12/17/2012 15:28
6Cazz12/17/2012 15:29
7gpywsgp12/17/2012 15:40
8
Sheet1





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: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/17/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
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
      If lc = 1 Then
        Cells(fr, lc + 2) = Format(Now, "m/d/yyyy h:mm")
      ElseIf lc > 2 Then
        Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
      End If
      Target.Clear
    End If
  End If
  .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


Please test the new Worksheet_Change Event in the office before going into the field.
 
Upvote 0
Hi Hiker,

I had tried with the code. It work well on the time stamp. But as for same delegate being scanned, the active cell skipped to next row. For example :


Delegate 1, 2 & 3 scanned.

Sheet1

ABCDEF
Name Time InTime OutTime InTime Out
Jacky
Penny
Steve

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 192px"><col style="WIDTH: 64px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]2012/12/18 13:14[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2012/12/18 13:14[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2012/12/18 13:14[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


--------------------------------------------------------------------------


If I scan delegate 1 for time out, the active cell skip to A6. Possible to remain the active cell at A5, even i scanned delegate 1 for time out?

Sheet1

ABCDEF
Name Time InTime OutTime InTime Out
Jacky
Penny
Steve

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 192px"><col style="WIDTH: 64px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]2012/12/18 13:14[/TD]
[TD="align: right"]2012/12/18 13:17[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2012/12/18 13:14[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2012/12/18 13:14[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
qpywsqp,

When you scan a delegate into the worksheet does your scanner routine then make the next blank cell in column A the active cell?

You did answer this question.

Be right back with another fix.
 
Upvote 0
qpywsqp,

When you begin to scan in delegates for the first time, the active cell should be cell A2.

If the active cell, for some reason, is not cell A2, then the macro will move the delegate to cell A2...

And, then if you move the active cell below the last recorded delegate (by 1 or more rows) the macro will remove the blank rows in column A.

The macro will make the active cell, the next available blank cell in column A.



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: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/18/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
      If lc = 1 Then
        Cells(fr, lc + 2) = Format(Now, "m/d/yyyy h:mm")
      ElseIf lc > 2 Then
        Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
      End If
      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
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


Please test the new Worksheet_Change event in the office before going into the field.
 
Upvote 0
Hi Hiker,

The final code you provided is work perfectly. That is what i am looking. Many appreciate on the help out. Thanks again. =)
 
Upvote 0
qpywsqp,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Developing a Lot Tracking Inventory system with QR Codes/Barcodes and Updating Ability
Replies
0
Views
344
Easilyy
E
A
  • Question Question
Help with scanning barcodes into an excel invoice
2
Replies
12
Views
1K
RoryA
RoryA
SkywardPalm
  • Question Question
Slow Typing Response from Bluetooth Scanner
Replies
3
Views
1K
Candyman8019
C
D
  • Solved
Time stamp using VBA
Replies
5
Views
1K
data808
D
D
  • Question Question
Barcode scanning tool
Replies
0
Views
963
D3FCON
D

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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