如何使用单个组合框元素引用来自不同工作表的单元格？

我可以将一些额外的数据附加到ComboBox吗？（示例代码假定使用UserForm）

当然，可以用二维数据块填充ComboBox。多列由.ColumnCount和.ColumnWidths属性定义。您甚至可以通过定义零宽度-cf隐藏列UserForm_Layout。此外，我演示了一种.List通过所谓的数组方法将数据分配给ComboBox的属性的快速方法。为了满足您的其他信息需求，在第2列和第3列（均被零宽度隐藏）中预先填充了对第1号或第2号工作表的引用，以及在所引用工作表中的“真实”行索引。

Private Sub UserForm_Initialize()
  ' Purpose: populate ComboBox with data from 2 sheets
    doFillCombo Me.ComboBox1, Sheet1, Sheet2   ' sheets reference via their CodeName here !
End Sub

Private Sub UserForm_Layout()
  ' Purpose: layout combobox including hidden columns
    With Me.ComboBox1
        .ColumnCount = 3                   ' << provide for 3 columns assigned via .List
        .ColumnWidths = .Width & ";0;0"    ' << hide last columns by ZERO widths
    End With
End Sub

子通话doFillCombo()（从调用UserForm_Initialize）

Private Sub doFillCombo(cbo As MSForms.ComboBox, _
            ws1 As Worksheet, ws2 As Worksheet, _
            Optional ByVal ColWs1 = "A", Optional ByVal ColWs2 = "A")
' assign data from both sheets to temporary arrays
  Dim tmp1, tmp2
  tmp1 = getData(ws1, ColWs1)
  tmp2 = getData(ws2, ColWs2)

' provide for a container array
  ReDim arr(1 To UBound(tmp1) + UBound(tmp2), 1 To 3)

' read 1st data block to container
  Dim i&
  For i = 1 To UBound(tmp1)
      arr(i, 1) = tmp1(i, 1)
      arr(i, 2) = 1                   ' refers to 1st worksheet
      arr(i, 3) = i                   ' item count in the sheet's data column
  Next i
' read 2nd data block to container
  Dim StartRow&: StartRow = UBound(arr) - UBound(tmp2) + 1
  For i = StartRow To UBound(arr)
      arr(i, 1) = tmp2(i - UBound(tmp1), 1)
      arr(i, 2) = 2                   ' refers to 2nd worksheet
      arr(i, 3) = i - UBound(tmp1)    ' item count in the sheet's data column
  Next i

' Assign data to combobox'es list property by one code line
  cbo.List = arr
End Sub

*过程调用的辅助函数 doFillCombo

    Private Function getData(ws As Worksheet, ByVal col, Optional ByVal StartRow& = 2) As Variant()
    ' Purpose: assign column data to variant array
    If IsNumeric(col) Then col = Split(ws.Cells(1, col).Address, "$")(1)
    Dim LastRow&
    LastRow = ws.Range(col & Rows.Count).End(xlUp).Row
    getData = ws.Range(col & StartRow & ":" & col & LastRow).Value2
    End Function

可能的测试显示以获取信息

假设一个标签控件（例如Label1）显示引用的工作表编号以及隐藏的组合框列中预填充的行索引：

Private Sub ComboBox1_Click()
' Purpose: display sheet related counters
' Note:    index reference to .List is zero-based (1st column = 0, 2nd = 1, ...)
  With Me.ComboBox1
      If .ListIndex < 0 Then Exit Sub
      Me.Label1 = "Sheet" & .List(.ListIndex, 1) & " " & _
                  "Item " & .List(.ListIndex, 2) ' optional test display via e.g. Label1
  End With
End Sub

顺便说一句，您无论如何也不会避免一些计算。在您的情况下，我什至建议您坚持使用您选择的方法，但要通过用户定义的函数获取索引-不必认为这很笨拙:-)。