New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Barcode Scanner with excel for stock control

Macros, VBA, Excel Automation, etc.

Barcode Scanner with excel for stock control

Postby bm555 » Mon Jun 27, 2016 10:47 pm

Hi all..new here and also new with excel code.
We have +-100 power tools which we lend to students/apprentices and we would like to keep track of these tools.
We have a barcode generator/printer and using excel 2010.
We have setup an excel spreadsheet with the scanned barcodes of the tools in column A.
What we are trying to achieve is:
When a tool is lent out, we scan it and in excel it should lookup the barcode and in the respective row in column B (Outgoing Stock) it should print the date.
When the tool is returned it would be scanned again and the date should be printer in the respective row (In Stock) column C.
Is this at all possible?
Thank you in advance
Gb
  • 0

bm555
Rookie
 
Posts: 4
Joined: Jun 27, 2016
Reputation: 0
Excel Version: 2010

Re: Barcode Scanner with excel for stock control

Postby NoSparks » Tue Jun 28, 2016 1:04 am

Hello bm555

This is possible using the Worksheet_Change event to automate things.
Scan everything into one particular cell, say B2.
Put this code into the sheet module.
Code: Select all
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim fndScan As Range
   
If Target.Address <> "$B$2" Then Exit Sub

With Range("A:A")
   
    Set fndScan = .Find(What:=Target.Value, _
                       After:=.Cells(.Cells.Count), _
                       LookIn:=xlValues, _
                       LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False)
   
    'to prevent this procedure from calling itself
    Application.EnableEvents = False
   
    If Not fndScan Is Nothing Then
        'just to show where found comment out later
        MsgBox Target.Value & " was found at  " & fndScan.Address
       
        With fndScan
            If .Offset(0, 1).Value = "" And .Offset(0, 2).Value = "" Then
            'both blank put in first
                fndScan.Offset(0, 1).Value = Now()
               
            ElseIf .Offset(0, 1).Value <> "" And .Offset(0, 2).Value = "" Then
            'only second blank put in second
                fndScan.Offset(0, 2).Value = Now()
               
            ElseIf .Offset(0, 1).Value <> "" And .Offset(0, 2).Value <> "" Then
            'both not blank, so clear both, put first
                fndScan.Offset(0, 1).Value = Now()
                fndScan.Offset(0, 2).Value = ""
            End If
        End With
       
    Else
        MsgBox Target.Value & " Not found"
    End If
End With

'clear and select B2 ready for next scan
Range("B2").ClearContents
Range("B2").Select

're-enable events
Application.EnableEvents = True

End Sub


Good Luck
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Barcode Scanner with excel for stock control

Postby bm555 » Tue Jun 28, 2016 1:32 am

Hi NoSparks and thanks for the code, appreciate it...sorry to be a pest, but I'm a real newbie at this, could you please advise regarding the worksheet change event?
As far as the code goes I right-clicked on the "sheet1" tab at the bottom and inserted it in the "view code" section..
Thanks again
  • 0

bm555
Rookie
 
Posts: 4
Joined: Jun 27, 2016
Reputation: 0
Excel Version: 2010

Re: Barcode Scanner with excel for stock control

Postby bm555 » Tue Jun 28, 2016 2:34 am

This is the worksheet...
  • 0

You do not have the required permissions to view the files attached to this post.
bm555
Rookie
 
Posts: 4
Joined: Jun 27, 2016
Reputation: 0
Excel Version: 2010

Re: Barcode Scanner with excel for stock control

Postby NoSparks » Tue Jun 28, 2016 9:14 am

Here's the adjusted file.

I've added a STOP statement to force a break in the code so you can better see what it does.
Also adjusted the Offsets to what I think/hope is what you require.

For testing purposes, just manually enter 123 as if it was a scan.
The procedure will stop with the Stop statement highlighted.
Use the F8 key to step through the code one line at a time and watch what's happening.
(If you hit the Window Key and Right Arrow both the sheet and code window should show at the same time)

CAUTION:
while testing, if you happen to exit the procedure after EnableEvents is set to False, and before EnableEvents is set back to True, nothing will seem to work any more with the scans. I know that's a vague statement but it will be extremely obvious.
THE FIX:
bring up the Immediate Window by hitting CTRL + G
type in Application.EnableEvents = True
hit Enter and close the Immediate Window

Hope all this is understandable.
Good Luck with your project.
  • 0

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Barcode Scanner with excel for stock control

Postby bm555 » Wed Jun 29, 2016 10:10 pm

Hi there and thanks for the help..I will try this one and let you know!

all the best
gb
  • 0

bm555
Rookie
 
Posts: 4
Joined: Jun 27, 2016
Reputation: 0
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 8 guests