Record condition when barcode items scan in Excel

Let’s take the frustration out of user forms

Become an Affiliate and earn 25% on Course Sales

For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.

Contact me regarding customizing this template for your needs.

Click for online Excel Consulting

I am able to provide online help on your computer at a reasonable rate.

I use a Blue condenser Microphone to record my videos, here is the link

Check out Crowdcast for creating your webinars

I use Tube Buddy to help promote my videos
Check them out

Follow me on Facebook

TWEET THIS VIDEO

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I’ve used or have experience with.
Templates with code are available for purchase

code for sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range(“A2”)) Is Nothing Then
Call access
Application.EnableEvents = True
End If
End Sub

code for module
Sub access()
Dim barcode As String
Dim rng As Range
Dim rownumber, fstrow As Long
Dim cell As Range
Dim status As String

‘define the cells on the scan in sheet
barcode = Sheet2.Cells(2, 1)
status = Sheet2.Cells(2, 2).Value
If barcode = “” Then Exit Sub

If barcode (does not equal) “” Then
‘search for barcode
Set ws = ThisWorkbook.Sheets(“Products”)
ws.Activate
‘search for the value in the “A” column
Set rng = ThisWorkbook.Sheets(“Products”).Range(“a:a”).Find(what:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
‘barcode not found
restart:
For Each cell In ThisWorkbook.Sheets(“Products”).Columns(1).Cells
‘looking for first blank column in Column “A”
If Len(cell) = 0 Then cell.Select: Exit For
Next cell
‘inserting all the information
ActiveCell.Value = barcode
ActiveCell.NumberFormat = “@”
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date & ” ” & Time
ActiveCell.NumberFormat = “d/m/yyyy h:mm AM/PM”
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = status
‘clearing the information from scan sheet
ThisWorkbook.Sheets(“scan”).Activate
ThisWorkbook.Sheets(“scan”).Cells(2, 2).Value = “”
ThisWorkbook.Sheets(“scan”).Cells(2, 1).Select
ThisWorkbook.Sheets(“scan”).Cells(2, 1).Value = “”

GoTo ende
Else
rng.Select
‘searching for the last occurance of that value
fstrow = rng.Row
rownumber = ThisWorkbook.Sheets(“Products”).Range(“a:a”).Find(what:=barcode, after:=ActiveSheet.Cells(fstrow, 1), _
searchdirection:=xlPrevious).Row
ThisWorkbook.Sheets(“Products”).Cells(rownumber, 3).Select
‘checking that the out field is empty
If Not IsEmpty(ActiveCell.Value) Then
GoTo restart
End If
‘if there is anything in the status cell, show a message with the status and end
ThisWorkbook.Sheets(“Products”).Cells(rownumber, 1).Select
If ThisWorkbook.Sheets(“Products”).Cells(rownumber, 4).Value (does not equal) “” Then
MsgBox ThisWorkbook.Sheets(“Products”).Cells(rownumber, 4).Value
GoTo ende
End If
‘if the status is empty enter out time
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = Date & ” ” & Time
ActiveCell.NumberFormat = “m/d/yyyy h:mm AM/PM”

End If

End If
ende:
‘clear the cells in the scan sheet and select the barcode scan cell
ThisWorkbook.Sheets(“scan”).Activate
ThisWorkbook.Sheets(“scan”).Cells(2, 1).Select
ThisWorkbook.Sheets(“scan”).Cells(2, 1) = “”
ThisWorkbook.Sheets(“scan”).Cells(2, 1).Select
End Sub

Watch more new videos about Excel Office | Synthesized by Mindovermetal English