VBA用户窗体联动ComboBox仅填充最后一项问题求助

问题根因 #

1. 循环内存在多余的直接赋值语句Me.CBProdFam = arrData(idx, 2)，每次匹配到符合条件的行都会覆盖组合框的当前显示值，最终默认显示为最后一次匹配到的第二列值，和你要加载的第三列名称列表无关。
2. VBA用户窗体组合框的List属性默认适配二维数组，直接传入一维数组会被识别为单行多列数据，单列显示场景下就会出现仅最后一个匹配值可见、其余项为空的异常。

修复代码 #

Private Sub CBLOB_Change()

Dim arrData As Variant
Dim arrProdFam As Variant
Dim mysearch As String
Dim cnt As Long
Dim idx As Long
Dim Searchrange As Range
    
    ' 加载前清空原有选项，避免旧数据残留
    Me.CBProdFam.Clear
    
    If Me.CBLOB.ListIndex <> -1 Then
        mysearch = Me.CBLOB.Value
    
        With Sheets("Lookups")
            Set Searchrange = .Range("N2", .Range("N" & .Rows.Count).End(xlUp))
        End With
    
        arrData = Searchrange.Resize(, 3).Value
    
        ReDim arrProdFam(1 To UBound(arrData, 1))
        
        For idx = LBound(arrData, 1) To UBound(arrData, 1)
            If arrData(idx, 1) = mysearch Then
                cnt = cnt + 1
                arrProdFam(cnt) = arrData(idx, 3)
            End If
        Next idx
        
        If cnt > 0 Then
            ReDim Preserve arrProdFam(1 To cnt)
            ' 转置一维数组为列向，适配组合框列表格式
            Me.CBProdFam.List = Application.Transpose(arrProdFam)
        Else
            MsgBox "Uh oh, things have gone a little sideways and the LOB " & mysearch & " cannot be located. Please try another Option!"
        End If
    Else
        MsgBox "Please select a LOB to search for!"
    End If
    
End Sub

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