Barcode System for Checking In and Out

[nightwing37]

I am writing a macro in VBA for an excel spreadsheet to manage barcode sign ins for a summer camp program.

Column A is Last Name
Column B is First Name
Column C is ID Number
All other columns will be alternating between AM check-in and PM check-in

This issue I am having that, upon scanning, the barcode value fills in the Active Cell, but fails to:
Update the first available cell in the same Row as the intersection of the Barcode Value and the existing Column C values

Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)






Dim thisRow As Long
Dim thisColumn As Long
Dim goTime As Long
goTime = 0


With Application
    .EnableEvents = False
    .ScreenUpdating = False
    
    Intersect(Target, Range("C:C")).Activate
    thisRow = ActiveCell.Row
    thisColumn = ActiveCell.Column
    While goTime < 1
    If Cells(thisRow, thisColumn) = vbEmpty Then
        Cells(thisRow, thisColumn) = Format(Now, "mm/dd/yy h:mm:ss")
        goTime = goTime + 1
    Else
        thisColumn = thisColumn + 1
    End If
    Wend
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

 

[sykes]

Unfortunately, the Worksheet_Change event, only fires once a cell has been updated - not whilst it's in the process of being updated - so your code will only fire, once the cell in question in no longer the active cell.
I'm guessing that if the user presses "Enter" after the barcode fires, you will achieve the desired result.
As long as the enter key behaviour on the worksheet in question, is set to highlight the next empty barcode cell, then only one Enter key stroke would be necessary, between barcode reads from the scanner.

......and in answer to your next question, no - I don't know how you'd get the code to fire purely on the cell data actually changing - only after it's been changed. There may be a way, just saying that I'm not aware of one.