Badge Scanning Tool - Need help resetting

[mlathem1998]

I had a tool built years back that would allow me to scan badges, then do a vlookup for the associated employee ID, and it time and date stamp the entry. After that pop down to the next line. The first tab is a scanning page with a button to launch the scanning tool, but it is also set to auto load when you open the file. There is a form control has a text box and clear list button. When you scan a badge with the right number of characters, it executes the vba code to find the employee number and drop it in A2. However, I can't get this to reset and it keeps popping up every time after the last entry even after I clear the list. So, I scan a badge, the next cell would be 430, then when I clear the list, it SHOULD go right back to A2. .

Sub Clean_List()
'
' Clean_List Macro
'

'
Columns("A:B").Select
ActiveSheet.Range("$A$1:$B$800").RemoveDuplicates Columns:=Array(1, 2), _
Header:=xlYes

End Sub



Private Sub CommandButton1_Click()
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("A1").Value = "EMPLOYEE ID"
Range("B1").Value = "SCANNED DATE"
TextBox1.SetFocus
Range("A2").Select
End Sub

Private Sub TextBox1_AfterUpdate()

End Sub

Private Sub TextBox1_Change()
If Len(Trim(TextBox1.Text)) = 8 Or TextBox1.Text = "" Then
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, -1).Select
ActiveCell.Value = TextBox1.Value
TextBox1.Text = ""
ActiveCell.Offset(-1, 1).Value = DateTime.Now


End If

End Sub



Can anyone help me figure out how to fix this? I know nothing about VBA really.

 

[diddi]

hi welcome to MrExcel.
when you say "scan" do you mean you are reading a barcode and looking up the ID to add to your attendance list

 

[mlathem1998]

I am scanning using a prox reader. It’s simply just taking the scribal badge number from the employee ID card and filling the text fiel in the scan box. After the digit code minimum is met, it executes the script, to vlookup the employee number and then date and time stamp the entry.

 

[mlathem1998]

Sorry, autocorrect will be the death of us. It is simply take the actual badge number. It is just acting as a wedge device.

 

[diddi]

see how this goes... i know there is an issue with the lookup which i assume you are doing on the worksheet, but it is one extra line of code you can add in and it removes all the formulae from your sheet.

Private Sub TextBox1_Change()
    Dim LastRow As Long
    If Len(Trim(TextBox1.Text)) = 8 Or TextBox1.Text = "" Then
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        With Sheets("Scan List")
            .Cells(LastRow + 1, 1) = TextBox1
            .Cells(LastRow + 1, 2) = Now
        End With
        TextBox1.Text = ""
    End If
End Sub

Private Sub CommandButton1_Click()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    With Sheets("Scan List")
        .Range(.Cells(2, 1), .Cells(LastRow, 2)).ClearContents
    End With
End Sub

 

[mlathem1998]

That gets is much closer. The clear list works and takes it back to A2. However, I am getting two timestamp lines now.

 

[NoSparks]

try removing Or TextBox1.Text = "" from the If statement
it won't stop the macro from running a second time but it will prevent the cells from being populated

 

[mlathem1998]

That solves the double timestamp, but leaves the text in the scan box. This sees the sixth digit and runs the script to populate the employee ID field and timestamp, but it doesn't reset and ready for the next scan.

 

[NoSparks]

Sorry, you'll also need to move TextBox1.Text = "" out of the If - End If

Private Sub TextBox1_Change()
    Dim LastRow As Long
    If Len(Trim(TextBox1.Text)) = 8 Then
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        With Sheets("Scan List")
            .Cells(LastRow + 1, 1) = TextBox1
            .Cells(LastRow + 1, 2) = Now
        End With
    End If
    TextBox1.Text = ""
End Sub

 

[mlathem1998]

Now that timestamps even when a single digit is keyed and nothing displayed in the employee ID cell.

Private Sub TextBox1_Change()
Dim LastRow As Long
If Len(Trim(TextBox1.Text)) = 6 Or TextBox1.Text = "" Then
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Scan List")
.Cells(LastRow + 1, 1) = TextBox1
.Cells(LastRow + 1, 2) = Now
End With
End If
TextBox1.Text = ""
End Sub

Private Sub CommandButton1_Click()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("Scan List")
.Range(.Cells(2, 1), .Cells(LastRow, 2)).ClearContents
End With
End Sub