VBA实现ComboBox显示唯一运单号并关联现有搜索宏

一、给ComboBox加载L列唯一运单号 #

在用户窗体的Initialize事件中添加代码，提取Tabelle1工作表L列的唯一值并加载到ComboBox：

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim uniqueShipments As Collection
    
    Set ws = ThisWorkbook.Worksheets("Tabelle1")
    Set uniqueShipments = New Collection
    
    lastRow = ws.Cells(ws.Rows.Count, "L").End(xlUp).Row
    
    ' 收集唯一运单号，忽略空值和重复值
    On Error Resume Next
    For Each cell In ws.Range("L2:L" & lastRow)
        If cell.Value <> "" Then
            uniqueShipments.Add cell.Value, Key:=CStr(cell.Value)
        End If
    Next cell
    On Error GoTo 0
    
    ' 加载到ComboBox
    For Each item In uniqueShipments
        Me.ComboBox1.AddItem item ' 替换为你实际的ComboBox控件名
    Next item
End Sub

二、ComboBox选择后聚焦输入框并筛选数据 #

给ComboBox的Change事件添加代码，实现选择运单号后的自动筛选和输入框聚焦：

Private Sub ComboBox1_Change()
    Dim ws As Worksheet
    Dim filterValue As String
    
    Set ws = ThisWorkbook.Worksheets("Tabelle1")
    filterValue = Me.ComboBox1.Value
    
    ' 清除原有筛选
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    ' 按选中的运单号筛选A:AA列（Field:=12对应L列，A列为第1列）
    If filterValue <> "" Then
        ws.Range("A1:AA1").AutoFilter Field:=12, Criteria1:=filterValue
    End If
    
    ' 聚焦搜索输入框
    Me.userinput.SetFocus ' 替换为你实际的输入框控件名
End Sub

三、关联现有搜索代码到筛选后的数据 #

修改你的现有搜索代码，让它只在筛选后的可见行中执行搜索逻辑，示例如下：

Sub YourExistingSearchMacro()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range
    Dim searchText As String
    Dim lastRow As Long
    
    Set ws = ThisWorkbook.Worksheets("Tabelle1")
    searchText = Me.userinput.Value
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 获取筛选后的可见行范围
    On Error Resume Next
    Set searchRange = ws.Range("A2:AA" & lastRow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ' 无可见数据时提示退出
    If searchRange Is Nothing Then
        MsgBox "当前运单号下无数据可搜索"
        Exit Sub
    End If
    
    ' 原有搜索逻辑，改为遍历可见行
    For Each cell In searchRange
        ' 你的搜索匹配代码（比如判断cell值是否包含searchText）
        ' ...
    Next cell
End Sub

注意事项 #

• 代码中的控件名称（ComboBox1、userinput）请替换为你窗体中实际的控件名称
• 如果数据表头不在第1行，需调整AutoFilter的表头范围和遍历起始行
• 测试前确保Tabelle1工作表L列有有效运单号数据

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