Hi Freak,
I write a lot of code for bar code scanning. A bar code scanner is the same as a card reader in that they are input devices that enter characters into a cell one character at a time. Unfortunately, there is no event for a cell as the characters are being entered, only after the selection moves to another cell is the change event fired. This is OK if you want to hit enter or click another cell after every swipe.
The better approach is to use a userform with a textbox that will receive the ID. A textbox has a KEYUP event that can count and analyze the characters as they are being input. The userform's modal property is set to false so that it can stay open while you perform other tasks if needed, such as erase an entry, and still monitor for a read from the card reader.
Here is how it works:
1. The code is set to look for IDs of a certain length, ex. all IDs have 5 characters bit this can easily be change to any length
2. Since the textbox retains the focus, all reads from the cardreader are dumped into the textbox one character at a time. Each character entered triggers the textbox's keyup event.
3. The textbox KeyUp event analyzes the length of the ID as each character is inputted and when that condition is met, the code performs an action.
4. The code compares the entered ID to count how many instances there are in column A. Base on what it finds, actions are performed based on the following scenarios
Scenario 1: No instances are found (Unique)- the ID is placed in col A and it is assumed that this therefor is a swipe in so the date and time are stamped in the in columns
Scenario 2: 1 or more instances are found- The code finds the last instance and determines if there was a swipe in and a swipe out. If there is a swipe in but no swipe out then the swipe out columns are stamped with a date and time. If there is both a swipe in and a swipe out then the code creates a new entry line placing the ID and a swipe in
The logic is fairly simple but manipulating the input of the characters to elicit an event with no user intervention is the trick.
Here is the code
In the user form module, place the following code:
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'------------------------------
'DECLARE AND SET VARIABLES
Dim Row As Long, LastRow As Long, NextRow As Long, x As Long
On Error Resume Next
If Len(TextBox1.Value) = 5 Then 'CHANGE TO THE LENGTH OF THE IDs
With Worksheets("Sheet1")
Row = 2
LastRow = 2
NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
x = WorksheetFunction.CountIf(Columns("A:A"), TextBox1)
'------------------------------
'NO PREVIOUS LOGIN
If x = "" Then
.Cells(NextRow, 1) = TextBox1
.Cells(NextRow, 2) = Date
.Cells(NextRow, 3) = Time
'------------------------------
'PREVIOUS LOGIN
Else
For I = 1 To x
Row = WorksheetFunction.Match(TextBox1, .Range("A" & Row + 1 & ":A" & NextRow), 0) + LastRow
LastRow = Row
Next I
Row = LastRow
'------------------------------
'PREVIOUS LOGIN- COMPLETED
If .Cells(Row, 2) <> "" And .Cells(Row, 4) <> "" Then
.Cells(NextRow, 1) = TextBox1
.Cells(NextRow, 2) = Date
.Cells(NextRow, 3) = Time
'------------------------------
'PREVIOUS LOGIN- NO OUT RECORDED
Else
.Cells(Row, 4) = Date
.Cells(Row, 5) = Time
End If
End If
TextBox1.Value = ""
End With
End If
End Sub
Private Sub UserForm_Activate()
TextBox1 = ""
End Sub
Attachment 539489
Attachment 539491
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by Logit
Bookmarks