Convert Column Headers to List Entries of a ComboBox in VBA/Excel

I have a combobox that is on a userform that I would like to populate with a list of values.

The values are in the middle of a series of columns and can be thought of as column headers as each is in its own column. The list of columns will expand over time and should be updated each time the user form is initialized. I have been able to create a list from a single column and I can also create a list from multiple columns but when I do, the values remain in a column orientation and I can't get them to transpose into a list of rows. The data in the columns looks like this:

|--|--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  |
|--|-----+-----+-----+-----+

I want the combobox list entries to appear like this:

d   a
e   b
f   c

This userform is initialized by another userform which selects from several options and then activates the correct worksheet from several worksheets that have similar information. When the second userform initializes, it should populate the combobox with the data in the example above. I created a list of values from a single column using:

Private Sub UserForm_Initialize()

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

End Sub

I can also get a list of all values in a row using:

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"

but this only selects one of the two rows I want and doesn't transpose the list of values into the correct format.

I've tried Information from here but this does not seem to apply to userforms. Information here was helpful in selecting the row correctly. This was helpful in selecting a dynamic field. Here I found information on transposing a list but I'm not sure I understood it completely. This relates to a listfill range but I'm not sure this applies to comboboxes on userforms either. This relates to inserting a transpose function but it didn't work for me.

Any help would be greatly appreciated.

Solution

Assign header info in rows 4 and 3 to Combobox

As far as I understand your post, you want to extract your header info starting from cell B3 to the last column in row 4, but to show data in reverse row order in a combobox.

You can assign these data to a variant 2-dim array, restructure it using advanced possibilities of the Application.Index function *) and assign the array to the Combobox'es .Column property in one line (in order to avoid a further retransposition via the mostly used .List property).

*) see Advanced possibilities of the Application.Index function

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