Record condition when barcode items scan in Excel
HTML-код
- Опубликовано: 12 сен 2024
- Record condition when barcode items scan in Excel. Ensure damaged items are not used from inventory. Free templates and templates with code are available for purchase for $50 USD
www.easyexcela...
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 www.calendly.co...
I am able to provide online help on your computer at a reasonable rate.
www.amazon.com...
I use a Blue condenser Microphone to record my videos, here is the link
amzn.to/37gyyGa
Check out Crowdcast for creating your webinars
app.linkmink.c...
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy....
Follow me on Facebook
/ easyexcel.answers
TWEET THIS VIDEO • Record condition when ...
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
*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 for $50 USD
email: easyexcelanswers@gmail.com to arrange purchase
How to insert VBA code in Excel
• How to insert VBA code...
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
Helpful concept
Glad you liked it
Useful
Glad to hear that
Barb I’m trying to create vba for inventory. Say I have 1000 washers. I want to scan the washer code and a pop up window appears and I enter how many washer I removed. Then inventory updates accordingly. Can you show us how to do this?
have you seen this video ruclips.net/video/0X4wjCLG_1c/видео.html ?