如何在VBA/Excel中用动态单元格区域给ListBox列表框赋值

核心问题出在以下两点：

• 单元格对象未绑定指定工作表：代码中Cells.Find、Cells(4, Columns.Count)两处直接调用了Cells、Columns集合，未指定所属工作表，默认取当前活动工作表的对应对象，若打开窗体时活动工作表不是「C&E」，会拿到错误的范围，甚至可能因找不到"cygnet code"导致xrange为空，后续赋值逻辑异常
• 区域数组维度和ListBox适配问题：你获取的rng是单行多列的单元格区域，该区域的.Value返回的是横向一维数组，而ListBox的.List属性默认按「行→列」的顺序加载二维数组，直接赋值单行数组会导致数据无法正常展示，仅显示空白行

Private Sub UserForm_Initialize()
    ' 修正隐式类型声明问题，原写法xrange会被识别为Variant而非Range类型
    Dim xrange As Range, newStartCell As Range
    Dim last_col As Integer
    Dim rng As Range

    Me.StartUpPosition = 0
    Me.Top = 300
    Me.Left = 475

    With ThisWorkbook.Sheets("C&E")
        ' 所有单元格对象均绑定当前工作表，避免活动表不一致问题
        ' Find起点固定为A1，避免ActiveCell位置异常导致找不到表头
        Set xrange = .Cells.Find(What:="cygnet code", After:=.Range("A1"), LookIn:=xlFormulas2, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        
        ' 增加判空逻辑，避免找不到指定表头时报错
        If xrange Is Nothing Then
            MsgBox "未找到表头「cygnet code」", vbExclamation
            Exit Sub
        End If
        
        Set newStartCell = xrange.Offset(, 1)
        last_col = .Cells(4, .Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(newStartCell.Row, newStartCell.Column), .Cells(newStartCell.Row, last_col))
    End With

    With Me.ListBox1
        ' 转置单行横向数组为纵向数组，适配ListBox逐行加载的规则
        .List = WorksheetFunction.Transpose(rng.Value)
        ' 转置后为单列数据，设置列数为1即可
        .ColumnCount = 1
    End With
End Sub

可选适配方案 #

如果需要将单行数据横向展示在ListBox的多列中，不需要转置数组，仅需调整ListBox列数配置即可：

With Me.ListBox1
    .List = rng.Value
    ' 列数和区域列数对齐
    .ColumnCount = rng.Columns.Count
    ' 可按需设置每列宽度，示例为3列各50宽度
    .ColumnWidths = "50;50;50"
End With

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