Stocktake of Barcoded Inventory in Excel using a Barcode Scanner

[Kobus Horn]

Good morning... I am struggling to get this figured out... Please help... I'm not an Excel Expert and would appreciate guidance

This is my Setup
* I have a Warehouse with about 6000 Unique Barcodes of Technical Equipment... One Unique Barcode for every piece of Equipment (Barcode looks as follows: AVB001234, AVB001235, AVB001236, AVB001237 etc etc etc)
* I want to do a Stocktake using a Barcode Scanner that has already been setup to work in Excel... The Scanner is currently setup in this way... I Choose a Cell, then Press the Trigger and when the Barcode is Scanned Barcode "Enters" into Cell and then goes to the Cell Below Automatically... In other words... every Barcode that gets scanned it will put in that Barcode in the Cell Chosen and then automatically moves to the cell below ready for scanning again (Firstly it needs to "GO BACK" to "E1" repeatably scan after scan after scan
* I need guidance with the following PLEASE

1. I need to setup the "SCANNING CELL - "E1" (Highlighted in Yellow) which was scanned using Barcode Scanner in such a way that I click there once with my Mouse to Highlight "E1"
2. Then Click the Trigger of the Barcode Scanner on a Unit on my Warehouse (Showing example scanned AVB002001 in "E1")
3. Then the Workbook must find that Barcode Scanned in Column "E" searching from Row 2 - 3000 in Column "E" finding the Corresponding Barcode "AVB002001"
... also keeping in mind that sometimes there will be some text behind the Barcode (Example - AVB001613 - Pro) that does NOT exist on the Barcode BUT it does exist on the Spreadsheet... so if there is Text behind the Barcode it must still find it - (Example - "AVB001613 - PRO") as shown on the Image in Cell "E40" - This was manually put in the Spreadsheet for extra descriptive text for "some" equipment
4. When the Barcode is found, put in a "YES" in Column "D" (STOCK) next to the same Barcode to "Mark" it as "SEEN"
5. Then change the Cell Color in Column "D" to Green to make it easier to see what needs to be still Scanned in the Warehouse
6. Once it has been "SEEN" and the "YES" put into Column "D" next to the corresponding Barcode and the Cell changed color to Green
7. IF the Barcode Scanned does NOT Exist in the Workbook

After the above has been done the following needs to happen...

RETURN to Cell "E1"
CLEAR the content scanned (Example AVB002001)
and be READY for the Next Barcode to be Scanned

This obviously needs to work in a Stocktaking manner where the Scanner can be Repeatably Triggered Click, After Click, After Click without needing to stop to change Cell after every Trigger...

*Needs to work as Follows -... "E1" Scan... Add YES, Change Color, Go back to E1 and Scan next... Scan Triggered every few seconds on next Barcode

I have to mention the fact that the Scanner when Scanned... automatically enters the Barcode (Example) "AVB002001" in the cell chosen and the Automatically goes to the next row below ready for the next scan...

I hope I have explained this in such a detailed way that someone can understand exactly what I need and PLEASE Help me

 

[sxhall]

Have some experience using barcodes with Excel, can you paste the code you have currently in to a reply? Click on the VBA icon when replying back and paste it between the ] and [.


Shouldn't be too difficult to get to what you need.

 

[Kobus Horn]

Have some experience using barcodes with Excel, can you paste the code you have currently in to a reply? Click on the VBA icon when replying back and paste it between the ] and [.


Shouldn't be too difficult to get to what you need.

Good day "sxhall"

Thank you for your reply

I'm very new to coding in Excel so I'm trying my best

I also think I'm not "Saving" correctly at the right place... When I put the code into the Module I click on the Blue-Save button and then I just close the VBA with the X on the top right in the Module and also the X on the right top of the VBA...

The steps I followed is as follows

1. Open "View Code" and Insert a Module
2. Put Code into Module 1
3. Return to Workbook on Top Lefthand side so that I can save the Workbook as "Makro Enabled"
4. Close Everything
5. Open Makro Enabled Workbook
6. Open Trust Center and enable VBA Macro's, Enable Excel 4.0 Macro's, Trust access to the VBA project
7. Click on Cell E1 and Scan a Barcode "AVB007986" to see if it works

8. ALL That happens is the Barcode is entered into CELL E1 and then Highlighted Block moves to E2... Nothing else happens

Here is the Code I put into the Module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim scanValue As String
Dim foundCell As Range

Set ws = ThisWorkbook.Sheets("Sheet1")

If Not Intersect(Target, ws.Range("E1")) Is Nothing Then
Application.EnableEvents = False

scanValue = ws.Range("E1").Value

If scanValue <> "" Then
Set foundCell = ws.Range("E:E").Find(What:=scanValue, LookIn:=xlValues, LookAt:=xlWhole)

If Not foundCell Is Nothing Then
foundCell.Offset(0, -1).Value = "X"
End If
End If

ws.Range("E1").ClearContents

Application.EnableEvents = True
End If
End Sub