基于另一ComboBox联动：实现UserForm中ComboBox2动态加载列标题

以下是修正后的完整代码，可直接实现需求：

Private m_Cancelled As Boolean

Public Property Get Cancelled() As Variant
    Cancelled = m_Cancelled
End Property

Private Sub ComboBox1_Change()
    Application.EnableEvents = False
    ComboBox2.Clear
    Application.EnableEvents = True
    
    ' 处理ComboBox1未选择的情况，避免报错
    If ComboBox1.Value = "" Then Exit Sub
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(ComboBox1.Value)
    
    Dim colCount As Integer
    colCount = 0
    
    ' 统计第一行非空列的数量
    Do While Not IsEmpty(ws.Cells(1, colCount + 1).Value)
        colCount = colCount + 1
    Loop
    
    ' 将列标题添加到ComboBox2
    Dim i As Integer
    For i = 1 To colCount
        ComboBox2.AddItem ws.Cells(1, i).Value
    Next i
End Sub

Private Sub ComboBox2_Change()

End Sub

Private Sub CommandButton1_Click()
    Hide
End Sub

Private Sub CommandButton2_Click()
    Hide
    m_Cancelled = True
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
    ComboBox1.Clear
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim wCount As Long: wCount = wb.Worksheets.Count
    Dim wsNames() As String: ReDim wsNames(1 To wCount)

    Dim ws As Worksheet, w As Long
    w = 0
    
    For Each ws In wb.Worksheets
        If ws.Visible = xlSheetVisible Then
            w = w + 1
            wsNames(w) = ws.Name
        End If
    Next ws

    If w < wCount Then ReDim Preserve wsNames(1 To w)
    ComboBox1.List = wsNames
End Sub

Private Sub UserForm_Activate()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then Cancel = True
    Hide
    m_Cancelled = True
End Sub

Function GetComboBox1() As String
    GetComboBox1 = ComboBox1.Value
End Function

关键修改说明： #

• 限定工作表引用：所有Cells操作前添加ws.前缀，确保操作的是ComboBox1选中的工作表，而非当前活动表。
• 添加空值判断：在ComboBox1_Change开头增加空值检查，避免未选择工作表时触发报错。
• 修正选项添加方法：用ComboBox2.AddItem替代错误的Cells(1, i).Add，这是向ComboBox添加选项的正确方式。
• 优化变量初始化：明确初始化colCount为0，避免循环逻辑异常。

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