Barcode Scanner

[Paarfus]

I manage a Calibration Lab and we use a barcode system to manage/action items in our logistical information system for calibration. However, there's no built in inventory function for current in shop items
and we often have 1,000+ items in shop. Our system does export to Excel however. What I'm looking to do is use the barcode scanner to not only search out in shop listing, but also action the cell/row when
There is a match for the barcode scanned.

Thanks


EDIT: I would simply just want to highlight the cell/row green to show the item was verified as on hand

 

[Anfinsen]

I manage a Calibration Lab and we use a barcode system to manage/action items in our logistical information system for calibration. However, there's no built in inventory function for current in shop items
and we often have 1,000+ items in shop. Our system does export to Excel however. What I'm looking to do is use the barcode scanner to not only search out in shop listing, but also action the cell/row when
There is a match for the barcode scanned.

Thanks


EDIT: I would simply just want to highlight the cell/row green to show the item was verified as on hand

Can you post an example of the output?

 

[Paarfus]

Can you post an example of the output?

 

[Paarfus]

View attachment 89600

Objective is to scan the item and have it search the Excel document and highlight the row green if there's a match.

 

[Paarfus]

Objective is to scan the item and have it search the Excel document and highlight the row green if there's a match.

Items in the shop are affixed with a barcode that corresponds with the codes in column B

 

[Anfinsen]

Items in the shop are affixed with a barcode that corresponds with the codes in column B

Hey Paarfus, I am assuming you know how to add code to a worksheet in this scenario. Let me know if you get stuck.

I setup a dummy worksheet, with columns labeled Column 1 thru 13. Your input Field will be Cell P1, as referenced in the screenshot below.

add the code below to your worksheet, and you should be able to scan values into P1, and this will search for the value. If the value isn't found, a message box pops up and notfies the user the value wasn't found, then cell P1 clears out.
Conversely, if the value is found, it will highlight the row, and then clear cell P1, enabling the cell to accept another entry. I tested this on my dummy worksheet and it works with no issue.

Private Sub Worksheet_Change(ByVal Target As Range)




Application.ScreenUpdating = False


If Range("P1") <> "" Then


Dim Barcode As String
Barcode = Range("P1")


Dim SearchRange As Range


Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row


Set SearchRange = Range("B2:B" & lastrow).Find(Barcode, LookIn:=xlValues, lookat:=xlWhole)


If SearchRange Is Nothing Then
MsgBox ("Value Not Found")




Range("P1").ClearContents
Exit Sub


Else


SearchRange.EntireRow.Interior.ColorIndex = 6
Range("P1").ClearContents
End If
End If
Application.ScreenUpdating = False
End Sub

 

[PhilDesanex]

Can you post an example of the output?

Couldn't you just use Ctrl-F (find), then scan the barcode in question. If there is a match, you can visit the line and see if the item is on hand.

 

[Paarfus]

Couldn't you just use Ctrl-F (find), then scan the barcode in question. If there is a match, you can visit the line and see if the item is on hand.

Hey Phil! Yes we could, but i'm looking to have Excel automate the function of highlighting the row green if the scanned barcode is found within the table. Manually highlighting the row green over thousands of items extends the amount of time considerably to conduct an inventory that produces a document that shows unfound items for investigation.

 

[Paarfus]

Hey Paarfus, I am assuming you know how to add code to a worksheet in this scenario. Let me know if you get stuck.

I setup a dummy worksheet, with columns labeled Column 1 thru 13. Your input Field will be Cell P1, as referenced in the screenshot below.

View attachment 89616

add the code below to your worksheet, and you should be able to scan values into P1, and this will search for the value. If the value isn't found, a message box pops up and notfies the user the value wasn't found, then cell P1 clears out.
Conversely, if the value is found, it will highlight the row, and then clear cell P1, enabling the cell to accept another entry. I tested this on my dummy worksheet and it works with no issue.

Private Sub Worksheet_Change(ByVal Target As Range)




Application.ScreenUpdating = False


If Range("P1") <> "" Then


Dim Barcode As String
Barcode = Range("P1")


Dim SearchRange As Range


Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row


Set SearchRange = Range("B2:B" & lastrow).Find(Barcode, LookIn:=xlValues, lookat:=xlWhole)


If SearchRange Is Nothing Then
MsgBox ("Value Not Found")




Range("P1").ClearContents
Exit Sub


Else


SearchRange.EntireRow.Interior.ColorIndex = 6
Range("P1").ClearContents
End If
End If
Application.ScreenUpdating = False
End Sub

I received an error and Debug took me to:

Set SearchRange = Range("B2:B" & lastrow).Find(Barcode, LookIn:=xlValues, lookat:=xlWhole)

Not sure what the error is or how to fix. Runtime error 1004?

 

[Anfinsen]

I received an error and Debug took me to:

Set SearchRange = Range("B2:B" & lastrow).Find(Barcode, LookIn:=xlValues, lookat:=xlWhole)

Not sure what the error is or how to fix. Runtime error 1004?

Are there any other sheets in your workbook? Did you insert this as a module, or add to the sheet's code?