can anyone help me set up a barcode scanner to display certain aspects on excel?

Dear @Carmelo Furnari,

Thank you for reaching out to Microsoft Q&A forum.

I understand that you're looking for a solution to set up your Netum NT1228BL barcode scanner to display multiple specific data points (Name, Phone, Serial, Model, Time IN, and Time OUT) in separate Excel columns, while also ensuring the data cannot be manually changed.

From my research, most basic barcode scanners (like the Netum NT1228BL) act just like a keyboard: they simply read the barcode data and rapidly "type" the resulting string of characters wherever the cursor is located in Excel. They don't have built-in logic to separate that single string into multiple columns automatically.

To achieve your goal of separating the data into Name, Phone, Serial, Model, etc., you need a system to tell Excel where to put each piece of information.

Phase 1: Scanner Setup & Data Entry

1. The Data Structure (The Barcode)

Your current barcode likely only contains a single ID (e.g., the Serial Number). To get all the fields you want, you need a different structure:

Barcode Lookup: The scanned barcode should contain a unique identifier (like the Serial Number). Excel will then use that Serial Number to look up the rest of the information (Name, Phone, Model) from a separate Master Data Sheet (see Phase 2).

2. Scanner Configuration for Automation

To make the scanner efficient for time tracking (Time IN/Time OUT), you must configure it:

I found that your scanner should automatically "press" the Enter key after scanning the data. This moves the cursor down to the next row, ready for the next scan. (If not, check your NT1228BL manual page 13 under Terminator for the programming barcode to add an Enter suffix, often called CR/LF).

Phase 2: Excel Setup (The Lookup Sheet)
1.You need two sheets in your workbook:

Sheet 1: Master Data (Lookup Table)

This sheet holds the information you want to display for every asset/person.

Sheet 2: Scan Log (Input Sheet)

This is the sheet where you scan the barcode, and the data appears.

In Column B through E, you will use the VLOOKUP or XLOOKUP function to pull data from the Master Data sheet.

For example, in cell B2 (Name), you would enter a formula like this:
=VLOOKUP(A2, Sheet1!A:E,2,FALSE)

This formula looks for the barcode you just scanned (A2) in the Master Data sheet and returns the value from the 2nd column (Name).

To lock the scanned data, you will use Excel's worksheet protection features. Users can only input data by scanning into the unlocked cell (A).

2: Automating Time IN/Time OUT and security with VBA

This requires Visual Basic for Applications (VBA), which is the most reliable way to implement time stamping in Excel.

Here is the VBA logic you would implement:

1. The VBA Code

You will need to open the VBA editor (Alt + F11), find the specific worksheet where you store your scanned ID(e.g., Sheet2)

Right click Sheet 2 > View code and paste the following code into the worksheet module. 

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Set the column where scanning occurs
    Const SCAN_COLUMN As Long = 1 ' Column A: Scanned Barcode
 
    ' Ensure the change is in the Scan Column and only one cell was changed
    If Target.Column = SCAN_COLUMN And Target.Cells.Count = 1 Then
        ' Exit if the cell was cleared (e.g., a user manually deleted the barcode)
        If IsEmpty(Target.Value) Then Exit Sub
        ' Disable events to prevent an infinite loop
        Application.EnableEvents = False
        ' The Barcode Scanned is the value to look for
        Dim ScannedID As String
        ScannedID = Target.Value
        ' Define the range to search (from row 2 down to the current row)
        Dim SearchRange As Range
        Set SearchRange = Range("A2:A" & Target.Row)
        ' --- LOOKUP LOGIC: Find last open "Time IN" for this ID ---
        Dim LastOpenRow As Long
        LastOpenRow = 0
        ' Loop backwards from the current row up to find the last Time IN without a Time OUT
        For i = Target.Row - 1 To 2 Step -1
            If Range("A" & i).Value = ScannedID Then
                ' Check if Time OUT (Col G) is empty for this ID
                If IsEmpty(Range("G" & i).Value) Then
                    LastOpenRow = i
                    Exit For ' Found the open Time IN, so stop searching
                End If
            End If
        Next i
        ' Temporarily unprotect the sheet to allow the VBA to write the time stamp
        ' REMINDER: Use your actual password, or "" if none.
        ActiveSheet.Unprotect "YourPassword"
        If LastOpenRow > 0 Then
            ' --- TIME OUT FOUND ---
            ' The worker is signing OUT. Fill the Time OUT cell on the LAST OPEN ROW.
            Range("G" & LastOpenRow).Value = Now()
            Range("G" & LastOpenRow).NumberFormat = "dd/mm/yyyy hh:mm:ss"
            ' Delete the barcode entered in the CURRENT row (Target) because it was just used for OUT logic
            Target.ClearContents
            ' OPTIONAL: Clear the VLOOKUP cells on the current row as well
            Range("B" & Target.Row & ":E" & Target.Row).ClearContents
            ' Move cursor back up to the row where the content was just deleted (ready for next scan)
            Target.Select
        Else
            ' --- TIME IN NOT FOUND / NEW RECORD ---
            ' The worker is signing IN. Log the current time on the current row.
            Target.Offset(0, 5).Value = Now() ' Col F (Time IN)
            Target.Offset(0, 5).NumberFormat = "dd/mm/yyyy hh:mm:ss"
 
            ' The scanner's ENTER key suffix will move the cursor down to the next row (A3)
        End If
 
        ' Re-protect the sheet
        ActiveSheet.Protect "YourPassword"
        Application.EnableEvents = True
    End If
End Sub

How this VBA works:

1. It verifies that the change happened only in Column A and for a single cell (ensuring it was a scan).
2. It temporarily disables Excel events to avoid infinite loops.
3. It unprotects the sheet so it can write Time IN/OUT.
4. It checks previous scans to determine whether the user is signing IN or OUT.
5. It writes the timestamp to the correct row.
6. It re-protects the sheet afterward.

Notes on Worksheet Protection:

The sheet protection process requires that the cells you scan into (Column A) are manually set to Unlocked before protection is applied.

1. Go to the Excel sheet. If it's protected, go to the Review tab and click Unprotect Sheet.
2. Select all of Column A (Scanned Barcode).
3. Right-click > Format Cells > Protection tab.
4. Ensure the box next to Locked is UNCHECKED.
5. Select all other columns (B through G) and ensure they are CHECKED (Locked).
6. Go to the Review tab and click Protect Sheet again, using the exact password you put in the VBA code (e.g., "YourPassword").

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this. Thank you for your patience and understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.