Excel条码库存 [英] Excel barcode inventory

问题描述

我正在尝试制作一个Excel工作簿来跟踪库存余额.现在，我的工作簿中设置了库存，存款和取款"表.库存表包含库存中每个项目的代码和数量.

I'm trying to make an Excel workbook to track a stock balance. Right now my workbook is set up with an Inventory, Deposit and Withdrawal sheets. Inventory sheet contains codes and quantity for each item in stock.

我想在存款"或提款"表的A1单元格中输入物料代码，然后程序应获取该编号，并查看其是否与库存"表中的任何内容匹配，如果匹配，则应在数量上加1该项的选择或删除取决于输入的存款或取款单.如果找不到匹配项，则应在库存"表中创建一个新项目.之后，应清除A1单元格.

I want to enter the item code in A1 cell on either the Deposit or Withdrawal sheet, then a program should take the number and see if it matches anything in the Inventory sheet, if it does then it should either add 1 to quantity of that item, or remove, depends on the Deposit or Withdrawal sheet, where input was made. If it can't find a match it should then create a new item in the Inventory sheet. After that, it should clear A1 cell.

我有一个Datalogic Quickscan条形码扫描仪，我将为库存中的每个物品创建一个条形码，然后使用扫描仪将条形码输入到工作表中.当我扫描条形码时，它只会输出一个数字，就像在连接到PC的传统键盘上键入的那样.

I have a Datalogic Quickscan barcode scanner, I will create a barcode for each item in stock, and use the scanner to enter barcodes to the worksheets. When I scan the barcode it just outputs a number, as it would be typed on a conventional keyboard connected to PC.

我受困于VBA代码，该代码将更新库存表.我有下面的代码，该代码在库存表中创建一个单元格，可以在其中扫描条形码，然后将其添加到列表中，但是如果我需要另一个可以扫描的单元格，并且从数量中减去该单元格，我该怎么办?

I'm stuck with VBA code which will update Inventory sheet. I have the below code which makes a cell in the inventory sheet where I can scan the barcode and then it adds it in the list, but what would I do if I need another cell where I can scan and that subtracts from the quantity instead?

Private Sub Worksheet_Change(ByVal Target As Range)

    Const SCAN_CELL As String = "F7"
    Const RANGE_BC As String = "A1:A500"
    Dim val, f As Range, rngCodes As Range

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub

    val = Trim(Target.Value)
    If Len(val) = 0 Then Exit Sub

    Set rngCodes = Me.Range(RANGE_BC)

    Set f = rngCodes.Find(val, , xlValues, xlWhole)
    If Not f Is Nothing Then
        With f.Offset(0, 2)
            .Value = .Value + 1
        End With
    Else
        Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
        f.Value = val
        f.Offset(0, 1).Value = "enter description"
        f.Offset(0, 2).Value = 1
    End If

    Application.EnableEvents = False
    Target.Value = ""
    Application.EnableEvents = True

    Target.Select

End Sub

推荐答案

以下是使用用户表单的解决方案.

Here is the solution with userform.

创建一个新工作表或将现有工作表重命名为库存.

Create a new worksheet or rename existing to Inventory.

创建用户窗体 UserForm1 ，如下所示:

Create a userform UserForm1 as shown below:

将代码放入 UserForm1 模块:

Option Explicit

Private pbModeDeposit As Boolean

Private Sub UserForm_Initialize()

    ' Setup header
    ThisWorkbook.Sheets("Inventory").Range("A1:C1").Value = Array("Item Code", "Description", "Quantity")
    ' Set Deposit mode
    pbModeDeposit = True
    ' Indicate current mode
    ShowMode

End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    Dim sItemCode As String
    Dim n As Long
    Dim i As Long
    Dim bExists As Boolean

    ' Check if enter key pressed
    If KeyCode = KeyCodeConstants.vbKeyReturn Then
        ' Cancel key to keep textbox in focus
        KeyCode = 0
        ' Check entire input code
        sItemCode = Me.TextBox1.Value
        Me.TextBox1.Value = ""
        Select Case True
            Case Not IsNumeric(sItemCode)
                ' Skip non-numeric values
                Exit Sub
            Case sItemCode = "10001990"
                ' Service code to switch to Deposit mode
                pbModeDeposit = True
                ShowMode
            Case sItemCode = "10000991"
                ' Service code to switch to Withdrawal mode
                pbModeDeposit = False
                ShowMode
            Case Else
                With ThisWorkbook.Sheets("Inventory")
                    .Range("A1:C1").Value = Array("Item Code", "Description", "Quantity")
                    ' Get last filled row number
                    n = .Cells(Rows.Count, 1).End(xlUp).Row
                    ' Check if scanned code exists
                    For i = 2 To n
                        bExists = .Cells(i, 1).Value = sItemCode
                        If bExists Then Exit For
                    Next
                    If bExists Then
                        ' Change quantity of existing item
                        .Cells(i, 3).Value = .Cells(i, 3).Value + IIf(pbModeDeposit, 1, -1)
                    Else
                        ' Add new item
                        .Cells(n + 1, 1).NumberFormat = "@"
                        .Cells(n + 1, 1).Value = sItemCode
                        .Cells(n + 1, 3).Value = IIf(pbModeDeposit, 1, -1)
                    End If
                End With
        End Select
    End If

End Sub

Private Sub CommandButton1_Click()

    ' Change mode
    pbModeDeposit = Not pbModeDeposit
    ' Indicate current mode
    ShowMode
    ' Keep textbox in focus
    Me.TextBox1.SetFocus

End Sub

Private Sub ShowMode()

    Me.CommandButton1.Caption = IIf(pbModeDeposit, "Deposit", "Withdrawal")

End Sub

将代码放入 ThisWorkbook 模块:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    With UserForm1
        .Show
        .CommandButton1.SetFocus
        .TextBox1.SetFocus
    End With

End Sub

此外，您可以将 UserForm1 属性 ShowModal 更改为 False .

Also, you may change UserForm1 property ShowModal to False.

扫描代码时，它将输入到 TextBox1 中.如果代码为 10001990 ，则切换存款模式，如果 10000991 ，然后为提款模式，则在文本框旁边的按钮上指示.仅以 10001990 和 10000991 为例，可以进行更改.任何其他数字输入都会产生库存清单的计算和更新.请注意，代码以文本形式存储，以避免大数字溢出或自动转换为工程符号E.

When you scan a code, it is input into TextBox1. If the code is 10001990 then Deposit mode switched, if 10000991 then Withdrawal mode, that is indicated on the button next to textbox. 10001990 and 10000991 just taken as the example and can be changed. Any other number input produces calculation and update of the inventory list. Note the codes are stored as text to avoid overflow or autoconversion to engineering notation E for big numbers.

这篇关于Excel条码库存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！