Excel/Macros in Excel


I am attempting to use a Macro to log training hours in an excel spreadsheet. Please help me! I work for a Not-for-profit organization. We provide trainings throughout the year that offer CEU hours to our members. I am responsible for tracking registration and attendance to our training sessions. We have recently decided to use ID Automation's Code 128 barcode font software to assign barcodes to each attendee (on their nametag)and use those barcodes to track their attendance and calculate their CEU hours. I have never used Macros and have no idea how to tell Excel what to do when the barcode is scanned.

My office manager wants to be able to scan the person's barcode, have the software find the barcode and (in the section of the table) place the corresponding earned CEU hours in the column corresponding to each class. When I scan a barcode, it just fills the space with the assigned ID. I'm completely lost. If you need further info please contact me at brandyemoore at gmail dot com. Thanks


say you scan the barcode into A1 of sheet1 so that the assigned ID appears in A1 of sheet1 at that point

assume your data starts in A2 of sheet2 with assigned ID's in column A (headers in row 1)

Assume CEU hours are entered into column M of the row with the appropriate assigned ID

Assume the CEU hours are 8

Right click on the sheet tab of Sheet1 and put in code like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, r As Range, r1 As Range, r2 As Range
Dim res As Variant
If Target.Address = "$A$1" Then
 Set sh = Worksheets("Sheet2")
 If Len(Trim(Target.Value)) = 0 Then Exit Sub
 ' set a reference to the list of IDs in column A of sheet2
 Set r = sh.Range("A2", sh.Cells(sh.Rows.Count, "A").End(xlUp))
 ' test if the value scanned in is found in that range
 If Application.CountIf(r, Target) > 0 Then
    ' the ID is in column A of sheet 2.  Determine it location
    res = Application.Match(Target.Value, r, 0)
    If Not IsError(res) Then
        ' set a reference to the cell with the ID
        Set r1 = r(res)
        ' set a reference to column M in that row
        Set r2 = sh.Cells(r1.Row, "M")
        ' Put in the CEU hours
        r2 = 8
        ' if this is additive then you would use this instead
        '  r2 = r2 + 8
        MsgBox Target.Value & " not found in data"
    End If
    MsgBox Target.Value & " not found in data"
  End If
End If
End Sub

the change event fires whenever you edit a cell on sheet1.  The barcode simulates typing and hitting enter to exit the cell.  So if you select A1 and scan in a value, the macro should locate that value in sheet2 and then put in 8 in column M of that row.

That is about the best I can do based on the information provided.

Tom Ogilvy
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.