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

[qpywsqp]

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?

 

[hiker95]

hongdiha,

Welcome to the MrExcel forum.

Sorry, what "Macro" do you refer to?

The macro code is in my reply #37.

I am sure that your workbook/worksheet is not set up as qpywsqp's is.


Please do not post your questions in threads started by others - - this is known as thread hijacking.
Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.
Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


Please start your own New Post and include:

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.


After you do the above, then send me a Private Message with a link to your New Post and I will have a look.

 

[Ednet]

hiker95,

Hi! I've been trying hard to use this macro program to run with my worksheet. If I already have an existing macro code on that same worksheet. Will it have any conflict? Or is it ok to just copy and paste the program you've created under the existing macro that I have?

Thanks alot!

qpywsqp,


Sample results with the updated Worksheet_Change Event:


Sheet1

	A	B	C	D	E	F	G
Code	Name	Contact	Email	Time In	Time Out	Time In
MC10008	Jacky
MC10080	Rebecca
PC10001	Rachel
PC10100	Jeffrey
PC10108	Steve
MC10988	Rosy
PC10945	Johnson
MC10884	Jackson
MC10886	Ravy
QPYWSQP

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:81px;"><col style="width:76px;"><col style="width:71px;"><col style="width:59px;"><col style="width:110px;"><col style="width:110px;"><col style="width:110px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

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

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

[TD="align: right"]2/4/2013 8:41[/TD]
[TD="align: right"]2/4/2013 8:43[/TD]

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

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

[TD="align: right"]2/4/2013 8:42[/TD]

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

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

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

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

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

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

[TD="align: right"]2/4/2013 8:42[/TD]
[TD="align: right"]2/4/2013 8:43[/TD]

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

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4




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

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 02/04/2013
' 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 <= 5 Then
      Cells(Target.Row, 5) = Format(Now, "m/d/yyyy h:mm")
    ElseIf lc > 4 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 < 5 Then
        Cells(fr, 5) = Format(Now, "m/d/yyyy h:mm")
      ElseIf lc > 4 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

Remember - you can only have one Worksheet_Change Event within a worksheet.

Then scan in the Code as before.

 

[hiker95]

Ednet,

Welcome to the MrExcel forum.

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Please do not post your questions in threads started by others - - this is known as thread hijacking.
Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.
Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

I've been trying hard to use this macro program to run with my worksheet.

I am sure that your worksheet is not identical to the worksheet by the creator/starter of this post, qpywsqp.

If I already have an existing macro code on that same worksheet. Will it have any conflict? Or is it ok to just copy and paste the program you've created under the existing macro that I have?

You can not have two Worksheet_Change event macros in the same worksheet.


Please start your own New Post and include:

What version of Excel are you using?

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

If you are not able to give us screenshots:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message with a link to your New Post, and, I will have a look.

 

[ncoates]

Hi all....

At the risk of hijacking, it seems this is exactly what I need, but I would like to know of the best scanner to use in this case? could I use a webcam?

Thanks for your help, if needs be then I can start a new thread.

Regards

Neil

 

[Anuj4888]

How do I create a new post. I want some similar solution to mark attendance. Please help. Urgently need it. Thanks in advance.

 

[orsm6]

Hi, how could this code be changed to still do what it is doing but copy the cell content on another sheet (FrontScreen!D13) and then paste it in column B next to the right cell in cell in column A?

cheers,

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

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.

 

[hiker95]

orsm6,

Please follow my instructions in my rely #43.

 

[orsm6]

Hi Hiker95, yes a new thread has already been started.

As it is your code i am referencing perhaps if you get some free time you might be able to have a quick look at the question? it's possible the answer is simply no, but i'd thought i'd ask someone.
thanks.

orsm6,

Please follow my instructions in my rely #43.

 

[mddrexel]

Hello Hiker95, I hope you're still around, I have a question, I love this code, it was perfect for what we needed but I have found that some people have been changing the times to make it look like their entries were done at different times. is there any way to lock the time entries once they are made?

Thank you so much

 

[hiker95]

I love this code, it was perfect for what we needed but I have found that some people have been changing the times to make it look like their entries were done at different times. is there any way to lock the time entries once they are made?

mddrexel,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Based on your description, screenshots will probably not work correctly.

To start off, and, so that we can get it right on the first try:

I would like to see your actual raw data workbook/worksheet(s).

The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.