Excel vba respond to barcode scan

[keith05281967]

Greetings VBA'ers,

I have an interesting challenge i'm trying to solve. I want to be able to scan a badge and have vba populate the person's name (from an array I guess) and the current date/time (now function) to a spreadsheet.

Lets say the barcode scan # would go in A2, then populate B2 w/ date/time, and C2 with name.

<TABLE style="WIDTH: 509pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=677><COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" span=4 width=141><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: #4f81bd; WIDTH: 85pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl63 height=21 width=113>Badge #</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 106pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=141>Date</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 106pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=141>Name</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 106pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=141>Time In</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 106pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=141>Time Out</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl64 height=21>45678 - I</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none"></TD></TR></TBODY></TABLE>

I recorded the code of the action of scanning a badge:

ActiveCell.FormulaR1C1 = "45678 - I"
Range("A2").Select

Here is the critical question: How can I get VBA to recognize "45678" was just added to cell A2 and then populate B2 and C2.

This would need to work for about 40 employees.

Has anyone done something like this?
Any help would be appreciated.
Keith

 

[Smitty]

I'd probably use VLOOKUP tied to the barcode.

Barcode input generally doesn't trigger a change event, but you can use the Calculate event to add the Date/Time stamp.

HTH,

 

[keith05281967]

I got VBA to recognize that a number was added by using a worksheet_change event. And I have it entering the date and time. Now I need it to take a number, like 45678, reference a table and return a name "Timmy Smith". Would I use an Array or a table or what? If you have the answer can u show an example?

thanks,
Keith

 

[Smitty]

I'd use a Lookup table that has Barcodes in column A, and Employee Names in column B. If the Change event is firing then you can use the Find method instead of VLOOKUP. There's a good example in the VBA helpfile.

 

[keith05281967]

ok, i'll try that. Thanks.

 

[Colby]

I have a spreadsheet that does this but with track scans from a magswipe (employee ID's). Its at work, PM me your email and I can send it your way. I use a VLookup and a custom form with validation VBA.

For barcode stuff, your barcode reader should be passing it as a keyboard emulator, you shouldn't have to parse out the binary.

 

[Colby]

Found enough code to put it together from another sheet:

Sub FigureoutEmployeeName()

    Dim nr As Long
        
    With Workbooks("EA_Schedule.xls").Sheets("QueryResults") 'Adjust as required
        nr = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("B" & nr).Formula = "=VLOOKUP(RC[-1],Data!C[0]:C[1],2,0)"
        .Range("C" & nr).Value = Now
    End With


End Sub