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

 

[sxhall]

Hi Kobus,

Have been away and just seen your reply, will see what I can come up with and will get back to you.

 

[Kobus Horn]

Hi Kobus,

Have been away and just seen your reply, will see what I can come up with and will get back to you.

Hi "SXHall"

Thank you for your reply

My stocktake starts on Monday 2 Dec and I would LOVE to have a working Excel Document that I can use with our Barcode Scanner to do Stocktake

The way we currently do it is very delayed and slow... I would LOVE an automated solution

I still can't figure it out, I actually gave up already

Looking forward in your reply

 

[sxhall]

Hi Kobus,

When you add the code in it needs to go in the sheet, which is where I think you found it. Double click on the 'Sheet1(Sheet1)' icon and it will open it.

Delete out the old code (save a copy if you want before deleting) and paste the code below in...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim scanValue As String
Dim foundCell As Range
Dim LRow As Long

Set ws = ThisWorkbook.Sheets("Sheet1")      'If you change the sheet name then change "Sheet1" here to the new sheet name.

Application.EnableEvents = False

If Not Intersect(Target, ws.Range("E1")) Is Nothing Then

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

LRow = ws.Range("A65536").End(xlUp).Row

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

        If Not foundCell Is Nothing Then

            With foundCell.Offset(0, -1)
                .Value = "Yes"
                .Interior.Color = RGB(146, 208, 83) 
            End With
        Else
        
        MsgBox scanValue & " not found in workbook!"             'Delete this row if you don't want the prompt
        Range("E65536").End(xlUp).Offset(1, 0).Value = scanValue 'Delete this row if you don't wan to keep a record f scans not found
        
        End If

    End If
    
With ws.Range("E1")
    .ClearContents
    .Activate
End With

End If

Application.EnableEvents = True

End Sub

You will see in the above two extra rows of code I've included...

• 1st row - shows a popup message box to alert that the barcode scan was not found
• 2nd row - adds any scans not found to the end of the data in column E

You can delete either one of these or both.

In my test I succesfully managed to find any scan with an suffix (rows 15, 19 & 22 below) or a prefix (row 25). It adds in the word 'Yes' and turns the cell colour green then returns to cel E1.
You can see that I have an entry n row 32 & 33 for scans that were not found.

Not found popup, will still return to cell E1 after pressing enter...

Hope this all good for you

 

[Kobus Horn]

OH MY GREATNESSSSSSSS

This is working 100% Absolutely Perfect

You have got no idea how Impressed and Greatful I am

This will save me HOURS of Work

I'm TRULY Appreciative of your time and efforts

The Warmest Kindest Regards
Kobus

 

[sxhall]

Glad to have helped

 

[Kobus Horn]

Hi "SXHall"

May I please ask that there's a BEEP Sound If Barcode was Found

and a BOOP Sound If there is an ERROR where Barcode was not found...




If there is a Sound difference between FOUND and NOT FOUND it will help that we don't need to look at the Monitor the whole time to make sure

Thank you in Advance

 

[Kobus Horn]

Hi "SXHall"

So we are busy with Stocktake now and the Document is going to work very well

But we're experiencing some difficulties

1. We need a BEEP Sound for Found and a BOOP Sound NOT Found

THEN

2. When the Barcode gets Scanned in the Yellow Block (E1) as it searched for the Barcode in the Whole Column "E" and then Marks the Appropriate Column "D" with a Yes and it changes the Block to GREEN

This works absolutely Brilliantly but...

I can't see which Item was found on the Left while it's changing the YES and making it GREEN... So I'm "Hoping" that it was checked while scanning

* Is there a Possibility when the Change happens from YES and GREEN in Column "B" that I can see this happen on the Screen... In other words can the GREEN BLOCK that got changed be the Top Line shown on the Screen (As in Screenshot)

PS: I did FREEZE TOP ROW to keep YELLOW Scanning Block Always Visible

I now need the row that was scanned to show underneath the YELLOW BLOCK so that I can confirm the Stock was Scanned with my eyes

SCANNED = E1 (AVB002067)
Found = D1644 (AVB002067) but the Row is showing underneath the SCANNED Block



Currently after every scan the Screen goes up to and stays on Row 1 - 44

and everything underneath is obviously not seen so I'm relying purely on "Hope"