I need help to create an Inventory sheet with a running balance and use a barcode scanner.

**enemitz** · 08-25-2017, 10:24 AM

Greeting's and Salutations,

Forgive me if I have asked this in the wrong area of the forum, as I am new. Let me begin by explaining a little about myself. I work for a 3rd party contractor that repairs and maintains large scale printing presses. I am responsible for a large amount of parts that I carry with me from site to site. I also have a storage building with extra parts that I can not carry with me. I have little to no experience with excel. I however am a fast learner and pretty well computer literate.

I have a site where I can download a file with what I am supposed to have in my possession. It downloads in an aspx file, but I can open it with excel and save it as an excel document. In the first column I have the part numbers. in the second column I have another part number listed as the prime number. Then I have an OHB. ON hand balance. This is what I am supposed to have in my possession. I have used my barcode scanner in excel before and it scans the code and enters in the corresponding part number. What I would like to do, is scan my parts into a column and as I do, it subtracts from the OHB Column. I have added the file in question and made a note in Column M.

Thank you in advance for any help you may provide.

**mehmetcik** · 08-25-2017, 01:08 PM

Hi

I personally have no idea which macro events your scanner will activate so cannot write a macro to run automatically as you describe.

What I can work with however is if your scanner stores numbers into a Sheet Called "Scanned"
Then when you activate sheet "snapohb" your macro will run.

When and if you can tell us if the Change event works then we can upgrade to that.

For now right click on the sheetname "snapohb" at the bottom of excel and select view code

Paste this code into the module that opens and close it.

Try scanning something and see it it triggers a macro

Right Click on the Sheet Name

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Change Event"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Selection Change Event"
End Sub

**enemitz** · 08-25-2017, 01:32 PM

I did what you asked and when I scanned it popped up a dialogue box that says, Change event with a box to click ok then another box pops up when you click okay that says, selection change event with an ok box.

**mehmetcik** · 08-25-2017, 01:37 PM

Thankyou.

That means that we can use the change event Macro.

Right Click on the Sheet Name at the bottom of excel and Select View Code

Delete Everything in the module that opens

Paste this in the module and close it.

Good Luck


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Or Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Set Z = Columns(3).Find(Target.Value, LookIn:=xlValues, Lookat:=xlPart)
If Z.Value = Target.Value Then
Cells(Z.Row, 4).Value = Cells(Z.Row, 4).Value - 1
Target.Value = ""
Else
MsgBox "Scanned Item Not Found", vbOKOnly
End If
On Error Resume Next
Application.EnableEvents = True
End Sub

**enemitz** · 08-25-2017, 01:39 PM

Awesome, what do you need me to do now? Thank you for helping me. I have tried to figure this out by watching YouTube videos and I am a total novice.

I need help to create an Inventory sheet with a running balance and use a barcode scanner.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

I need help to create an Inventory sheet with a running balance and use a barcode scanner.

Re: I need help to create an Inventory sheet with a running balance and use a barcode scan

Re: I need help to create an Inventory sheet with a running balance and use a barcode scan

Re: I need help to create an Inventory sheet with a running balance and use a barcode scan

Re: I need help to create an Inventory sheet with a running balance and use a barcode scan

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Inventory Mangement - Barcode Scanner

Update an Inventory using barcode barcode scanner

Can Excel be used with a barcode scanner to track inventory?

Trying to track inventory using a barcode scanner in Excel

How to use barcodes / barcode scanner for fluctuating inventory levels

Using a barcode scanner to track inventory with differnent employees

Using a barcode scanner and UPC master sheet for Inventory Control

Bookmarks

Bookmarks

Posting Permissions