在VBA/Excel中将列标题转换为组合框的列表项

Question

我有一个用户表单上的组合框，我想用一个值列表填充它。

这些值位于一系列列的中间，可以被认为是列标题，因为每个列都在自己的列中。列的列表会随着时间的推移而扩展，每次初始化用户表单时都应该更新。我已经能够从单个列创建列表，也可以从多个列创建列表，但是当我这样做时，值保持在列方向，我无法将它们转置到行列表中。列中的数据如下所示：

|--|--A--+--B--+--C--+--D--+ ... +
|--|-----+-----+-----+-----+
|1 |     |     |     |     |
|--|-----+-----+-----+-----+
|2 |  << other data here >>|        
|--|     +-----+-----+-----+
|3 |  "  |  a  |  b  |  c  | ...    <~~ row 3 data (needed as 2nd element in combobox)
|--|     +-----+-----+-----+
|4 |  "  |  d  |  e  |  f  | ...    <~~ row 4 data (needed as 1st element in combobox)
|--|     +-----+-----+-----+
|5 |  "  |  1  |  2  |  3  |
|--|     +-----+-----+-----+
|6 |  "  |  4  |  5  |  6  |
|--|-----+-----+-----+-----+
|7 |  "  |  7  |  8  |  9  |
|--|-----+-----+-----+-----+

我希望组合框列表条目如下所示：

d   a
e   b
f   c

这个用户表单由另一个用户表单初始化，该用户表单从几个选项中选择，然后从几个具有相似信息的工作表中激活正确的工作表。当第二个用户表单初始化时，它应该用上面例子中的数据填充combobox。我用下面的代码从一列中创建了一个值列表:

Private Sub UserForm_Initialize()

    'ReferenceCombo.ColumnCount = 2
    'Range("B4", Range("B" & Rows.Count).End(xlUp)).Name = "Dynamic"
    'Me.ReferenceCombo.RowSource = "Dynamic"

End Sub

我还可以使用以下命令获取一行中所有值的列表：

Dim sht As Worksheet
Set sht = ActiveSheet

    ReferenceCombo.ColumnCount = 2
    sht.Range(Sheet7.Cells(4, 2), Sheet7.Cells(4, Columns.Count).End(xlToLeft)).Name = "Dynamic"
    sht.Range(Sheet7.Cells(4, 2), Sheet7.Cells(4, Columns.Count).End(xlToLeft)).Select
    Me.ReferenceCombo.RowSource = "Dynamic"

但这只选择了我想要的两行中的一行，并没有将值列表转换为正确的格式。

我从这里尝试过信息，但这似乎不适用于用户表单。这里的信息有助于正确选择行。这有助于选择动态字段。在这里，我找到了关于转置列表的信息，但我不确定我是否完全理解。这与列表填充范围有关，但我不确定这是否适用于用户表单上的组合框。这与插入转置函数有关，但对我不起作用。

任何帮助都将不胜感激。

Answer 1

将第 4 行和第 3 行中的标题信息分配给组合框

据我了解您的帖子，您希望提取从单元格 B3 到第 4 行最后一列的标题信息，但在组合框中以相反的行顺序显示数据。

您可以将这些数据分配给一个可变的二维数组，并使用＜code＞应用程序的高级可能性对其进行重构。索引function*）并将数组分配给组合框。一行中的列属性（为了避免通过常用的.Listproperty进行进一步的重传）。

*)请参见＜code＞应用程序的高级可能性。索引函数

Option Explicit                     ' declaration head of Userform code module

Private Sub UserForm_Initialize()
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' [1] assign data to variant 1-based 2-dim array v
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Dim v As Variant, nCols As Long
  nCols = Sheet7.Range(Sheet7.Cells(4, 2), Sheet7.Cells(4, Columns.Count).End(xlToLeft)).Columns.Count
  v = Sheet7.Range("B1").Resize(4, nCols).Value2          ' e.g. B1:X4 (if X4 is last column)
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' [2] restructure array by filtering rows 4,3 and all columns Array(1,2,3,...)
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  v = Application.Index(v, Application.Transpose(Array(4, 3)), allCols(nCols))
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' [3] assign data to combobox via .Column property
'      (instead of assigning the transposed array to the .List property)
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Me.ReferenceCombo.Column = v
End Sub

Private Sub UserForm_Layout()
  Me.ReferenceCombo.ColumnCount = 2
End Sub

Private Function allCols(ByVal ColNum As Long) As Variant()
' Purpose: return array with column numbers from 1,2, to ...ColNum
  ReDim temp(0 To ColNum - 1)
  Dim i As Long
  For i = LBound(temp) To UBound(temp)
      temp(i) = i + 1
  Next i
  allCols = temp
  End Function