基于Excel宏的POS应用：实现E3可搜索商品名列表及联动功能

1. 给E3添加可搜索商品下拉列表 #

普通数据验证无法实现实时搜索，推荐用ActiveX组合框实现，操作简单且体验流畅：

• 打开「开发工具」选项卡 → 「插入」→ 选择「组合框（ActiveX控件）」，拖动到E3单元格位置并调整大小匹配单元格。
• 右键组合框 → 「属性」，设置以下参数：
  • LinkedCell: $E$3（让选择的内容同步到E3单元格）
  • ListFillRange: 你的商品名称列范围（例如Sheet2!A2:A100，假设商品数据存在Sheet2的A列，从A2开始）
  • MatchEntry: 2 - fmMatchEntryComplete（输入时自动匹配并筛选列表）
• 设置完成后，在E3输入字符时，组合框会实时显示匹配的商品名称，直接选择即可。

如果不想用ActiveX控件，可采用「数据验证+VBA过滤」方案：

• 先定义动态名称：「公式」→「定义名称」，命名为SearchableItems，公式输入：

  =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)
  

• 给E3设置数据验证：选择「序列」，来源填=SearchableItems。
• 在工作表的VBA代码窗口添加以下代码（右键工作表标签→「查看代码」）：

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Address <> "$E$3" Then Exit Sub
      
      Target.Validation.Delete
      Dim searchKey As String, filteredArr As Variant
      searchKey = "*" & Target.Value & "*"
      filteredArr = Filter(Application.Transpose(Sheet2.Range("A2:A" & Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row)), searchKey, True, vbTextCompare)
      
      If UBound(filteredArr) >= 0 Then
          With Target.Validation
              .Add Type:=xlValidateList, Formula1:=Join(filteredArr, ",")
              .InCellDropdown = True
          End With
      End If
  End Sub
  

  注：此方案需点击下拉箭头才能看到过滤后的列表，体验略逊于ActiveX组合框。

2. 实现双向自动填充功能 #

在工作表的VBA代码窗口添加以下代码，整合「选商品名填ID/价格/数量」和「输ID填商品名/价格/数量」的双向功能：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Sheets("Sheet2") ' 替换为你的商品数据工作表名
    Dim matchRow As Long
    
    ' 当E3选择/输入商品名时，自动填充E10(ID)、F6(价格)、F8(数量)
    If Target.Address = "$E$3" And Target.Value <> "" Then
        On Error Resume Next
        matchRow = wsData.Range("A:A").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
        On Error GoTo 0
        
        If matchRow > 0 Then
            Me.Range("E10").Value = wsData.Cells(matchRow, "B").Value ' B列为item ID
            Me.Range("F6").Value = wsData.Cells(matchRow, "C").Value ' C列为价格
            Me.Range("F8").Value = wsData.Cells(matchRow, "D").Value ' D列为数量
        Else
            Me.Range("E10,F6,F8").ClearContents
        End If
    End If
    
    ' 当E10输入item ID时，自动填充E3(商品名)、F6(价格)、F8(数量)
    If Target.Address = "$E$10" And Target.Value <> "" Then
        On Error Resume Next
        matchRow = wsData.Range("B:B").Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
        On Error GoTo 0
        
        If matchRow > 0 Then
            Me.Range("E3").Value = wsData.Cells(matchRow, "A").Value ' A列为商品名
            Me.Range("F6").Value = wsData.Cells(matchRow, "C").Value
            Me.Range("F8").Value = wsData.Cells(matchRow, "D").Value
        Else
            Me.Range("E3,F6,F8").ClearContents
        End If
    End If
End Sub

注意事项 #

• 替换代码中的Sheet2为你实际存放商品数据的工作表名称。
• 根据你的数据结构调整列号：比如如果item ID在D列，就把wsData.Cells(matchRow, "B")改成wsData.Cells(matchRow, "D")。
• 确保Excel启用宏功能，否则代码无法运行。

内容的提问来源于stack exchange，提问作者Oluwasoresamuel