Barcode Check out from an inventory

Private Sub CommandButton1_Click()
Dim sn As String
Dim wsLog As Worksheet
Dim lastRow As Long
Dim currentTime As String

Set wsLog = ThisWorkbook.Sheets("Log")
sn = wsLog.Range("C1").Value ' Scan input cell
currentTime = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")

If sn = "" Then
MsgBox "Please scan a barcode."
Exit Sub
End If

' Check if item was already scanned out
Dim foundRow As Long
Dim i As Long
foundRow = 0
For i = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row To 3 Step -1
If wsLog.Cells(i, 2).Value = sn Then
foundRow = i
Exit For
End If
Next i

If foundRow > 0 And wsLog.Cells(foundRow, 4).Value = "" Then
' Log Check-In
wsLog.Cells(foundRow, 4).Value = currentTime
wsLog.Cells(foundRow, 5).Formula = "=D" & foundRow & "-C" & foundRow
MsgBox "Checked IN successfully!"
Else
' Log Check-Out
lastRow = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row + 1
wsLog.Cells(lastRow, 2).Value = sn
wsLog.Cells(lastRow, 3).Value = currentTime
MsgBox "Checked OUT successfully!"
End If

wsLog.Range("C1").Value = "" ' Clear scan box
End Sub

Here's what I used so far now I changed B2 to C1 to put it in the space I wished
 
Hello Ehunt256,

You're doing an amazing job pushing through each challenge, and your persistence is really paying off! Let’s walk through your three key requests and update your macro accordingly so you can finish strong

Code: 
Private Sub CommandButton1_Click()
    Dim sn As String
    Dim wsLog As Worksheet
    Dim wsInventory As Worksheet
    Dim lastRow As Long
    Dim currentTime As String
    Dim invRow As Long
    Dim found As Boolean
    Dim i As Long, j As Long

    Set wsLog = ThisWorkbook.Sheets("Log")
    Set wsInventory = ThisWorkbook.Sheets("Inventory")
    sn = wsLog.Range("C1").Value
    currentTime = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")

    If sn = "" Then
        MsgBox "Please scan a barcode.", vbExclamation
        Exit Sub
    End If

    ' Search SN in Inventory Sheet Columns B, C, D
    found = False
    For i = 2 To wsInventory.Cells(wsInventory.Rows.Count, "B").End(xlUp).Row
        For j = 2 To 4 ' Columns B to D (2 to 4)
            If Trim(wsInventory.Cells(i, j).Value) = Trim(sn) Then
                found = True
                invRow = i
                Exit For
            End If
        Next j
        If found Then Exit For
    Next i

    If Not found Then
        MsgBox "❌ This Serial Number was NOT found in Inventory.", vbCritical
        wsLog.Range("C1").Value = ""
        Exit Sub
    End If

    ' Check if item was already OUT in Log (look for last unmatched OUT entry)
    Dim scanFoundRow As Long
    scanFoundRow = 0
    For i = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row To 2 Step -1
        If wsLog.Cells(i, 2).Value = sn And wsLog.Cells(i, 4).Value = "" Then
            scanFoundRow = i
            Exit For
        End If
    Next i

    If scanFoundRow > 0 Then
        ' Log Check-IN
        wsLog.Cells(scanFoundRow, 4).Value = currentTime
        wsLog.Cells(scanFoundRow, 5).Formula = "=D" & scanFoundRow & "-C" & scanFoundRow
        MsgBox "Checked IN successfully!", vbInformation
    Else
        ' Log Check-OUT
        lastRow = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row + 1
        wsLog.Cells(lastRow, 2).Value = sn
        wsLog.Cells(lastRow, 3).Value = currentTime
        MsgBox "📦 Checked OUT successfully!", vbInformation
    End If

    ' Clear Scan Input
    wsLog.Range("C1").Value = ""
End Sub

What This Code Now Does:
  • Scans the Inventory sheet (Columns B to D) for a match.
  • If not found, it shows a “Not Found” message and skips logging.
  • If found, it logs:
    • Check-OUT first (by default).
    • Check-IN if that SN already has an OUT entry without a matching IN.
  • Clears the scan input after every action.
Tips for Final Touches:
  • Make sure your Inventory sheet has values in columns B, C, or D starting from row 2.
  • Save your workbook as .xlsm to retain macros.
  • You can later expand this to update inventory status or quantity if needed.
 
shamimarita said:
Hello Ehunt256,

You're doing an amazing job pushing through each challenge, and your persistence is really paying off! Let’s walk through your three key requests and update your macro accordingly so you can finish strong
Click to expand...
I try to be persistent enough to get and understanding of what I'm learning and I'm sorry for all the confusion trying to explain what I wanted to learn, thank you for your patience to helping understand this better.
shamimarita said:
Code: 
Private Sub CommandButton1_Click()
    Dim sn As String
    Dim wsLog As Worksheet
    Dim wsInventory As Worksheet
    Dim lastRow As Long
    Dim currentTime As String
    Dim invRow As Long
    Dim found As Boolean
    Dim i As Long, j As Long

    Set wsLog = ThisWorkbook.Sheets("Log")
    Set wsInventory = ThisWorkbook.Sheets("Inventory")
    sn = wsLog.Range("C1").Value
    currentTime = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")

    If sn = "" Then
        MsgBox "Please scan a barcode.", vbExclamation
        Exit Sub
    End If

    ' Search SN in Inventory Sheet Columns B, C, D
    found = False
    For i = 2 To wsInventory.Cells(wsInventory.Rows.Count, "B").End(xlUp).Row
        For j = 2 To 4 ' Columns B to D (2 to 4)
            If Trim(wsInventory.Cells(i, j).Value) = Trim(sn) Then
                found = True
                invRow = i
                Exit For
            End If
        Next j
        If found Then Exit For
    Next i

    If Not found Then
        MsgBox "❌ This Serial Number was NOT found in Inventory.", vbCritical
        wsLog.Range("C1").Value = ""
        Exit Sub
    End If

    ' Check if item was already OUT in Log (look for last unmatched OUT entry)
    Dim scanFoundRow As Long
    scanFoundRow = 0
    For i = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row To 2 Step -1
        If wsLog.Cells(i, 2).Value = sn And wsLog.Cells(i, 4).Value = "" Then
            scanFoundRow = i
            Exit For
        End If
    Next i

    If scanFoundRow > 0 Then
        ' Log Check-IN
        wsLog.Cells(scanFoundRow, 4).Value = currentTime
        wsLog.Cells(scanFoundRow, 5).Formula = "=D" & scanFoundRow & "-C" & scanFoundRow
        MsgBox "Checked IN successfully!", vbInformation
    Else
        ' Log Check-OUT
        lastRow = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row + 1
        wsLog.Cells(lastRow, 2).Value = sn
        wsLog.Cells(lastRow, 3).Value = currentTime
        MsgBox "📦 Checked OUT successfully!", vbInformation
    End If

    ' Clear Scan Input
    wsLog.Range("C1").Value = ""
End Sub

What This Code Now Does:
  • Scans the Inventory sheet (Columns B to D) for a match.
  • If not found, it shows a “Not Found” message and skips logging.
  • If found, it logs:
    • Check-OUT first (by default).
    • Check-IN if that SN already has an OUT entry without a matching IN.
  • Clears the scan input after every action.
Tips for Final Touches:
  • Make sure your Inventory sheet has values in columns B, C, or D starting from row 2.
  • Save your workbook as .xlsm to retain macros.
  • You can later expand this to update inventory status or quantity if needed.
Click to expand...
Yep, I think this covers it if there any issues I will chalk them to user error and attempt to correct.
as for learning VBA I still got a ways to go to fully learn it, BUT I see the practical applications and it's worth learning, here's hoping I can master Excel in the future.

Runtime error 13 type mismatch it has something to do with this line. Any ideas?
If Trim(wsInventory.Cells(i, j).Value) = Trim(sn) Then

Ok this seems to fix it I had to internet solve this one
If Trim(CStr(wsInventory.Cells(i, j).Value)) = Trim(sn) Then
 
Last edited:
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
28
Total visitors
28
Totals may include hidden visitors.

Forum statistics

Threads
416
Messages
1,845
Members
901
Latest member
JORGE W: ROSERO

Share this page

Back
Top