Excel VBA 用组合框替换数据验证列表

按住Ctrl+t将您的列表范围转换为表格。此代码适用于 ActiveX ComboBox 或 ListBox。MsFormsListName 是具有控件的工作表的名称。该表可以位于调用子例程的工作簿中的任何位置。

Sub Test()
    AddTableRowAsListColumn "Table2", "ComboBox1", "Sheet2", 1
    AddTableRowAsListColumn "Table2", "ListBox1", "Sheet2", 1
End Sub

Sub AddTableRowAsListColumn(TableName As String, MsFormsListName As String, MsFormsListWorksheetName As String, RowIndex As Long)
    Dim MsFormsList As OLEObject
    On Error Resume Next
    Set MsFormsList = Worksheets(MsFormsListWorksheetName).OLEObjects(MsFormsListName)
    On Error GoTo 0

    If MsFormsList Is Nothing Then
        MsgBox MsFormsListName & " not found on " & MsFormsListWorksheetName
        Exit Sub
    End If

    MsFormsList.Object.list = WorksheetFunction.Transpose(Range(TableName).Rows(RowIndex).Value)
End Sub

Sub AddTableColumnToList(TableName As String, MsFormsListName As String, MsFormsListWorksheetName As String, ColumnIndex As Long)
    Dim MsFormsList As OLEObject
    On Error Resume Next
    Set MsFormsList = Worksheets(MsFormsListWorksheetName).OLEObjects(MsFormsListName)
    On Error GoTo 0

    If MsFormsList Is Nothing Then
        MsgBox MsFormsListName & " not found on " & MsFormsListWorksheetName
        Exit Sub
    End If

    MsFormsList.Object.list = Range(TableName).Columns(ColumnIndex).Value
End Sub