How would I loop through table headers to populate a ComboBox drop-down list?

Question

I'm trying to loop through a table's header row to populate a UserForm's ComboBox drop-down list.

The header row of my table is the second row of my worksheet.

My thought was to use the intersect() function to check whether my loop is still inside my header range and if not then exit the loop.
If the intersect is true, then add the current cell.value to the ComboBox list.

I am also trying to store the header row into an array, but I haven't been able to start my loop to see if my code sets the header row values to an array.
The error that I am getting is

'Object variable or with block variable not set'

Option Explicit

Dim ws As Worksheet

Public Tbl1 As ListObject


Private Sub ComboBox_DropButtonClick()

Set ws = ActiveSheet
Set Tbl1 = ws.ListObject("Table1")

Dim i As Integer
Dim Tbl1HeaderArray() As Variant

i = 1
ComboBox.RowSource = ""

Do Until i = -1

    If Intersect(Tbl1.HeaderRowRange(), ws.Cells(2, i)) Is Nothing Then
        i = -1
    Else
        ComboBox.AddItem (ws.Cells(2, i).Value)
        Tbl1HeaderArray(i - 1) = ws.Cells(2, i).Value
        i = i + 1
    End If

Loop

End Sub

I've tried a couple different approaches, but this is the most promising. I'm open to different routes of accomplishing my task.

I set it as a public list object in the Option Explicit section. Is that not the correct way to go about that? — Ira Banks, Nov 09 '20 at 21:53
You declared it but you never `Set` it, for example `Set Tbl1 = ActiveSheet.ListObjects(1)`. — BigBen, Nov 09 '20 at 21:55
You could just grab the Tbl1.HeaderRowRange.value from the listobject and transpose into 1d array https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables You can assign arrays (correctly transposed) into combobox https://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html — QHarr, Nov 09 '20 at 22:00

VBasic2008 · Accepted Answer · 2020-11-10T20:11:48.107

2

Fill Combo Box With Table Headers

The Code

Option Explicit

Private Sub ComboBox1_DropButtonClick()
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Table1")
    
    With ComboBox1
        '.Clear
        .Column = tbl.HeaderRowRange.Value
    End With
    
End Sub

edited Nov 10 '20 at 20:11

answered Nov 09 '20 at 22:48

VBasic2008

44,888
5
17
28

The values are populating in the drop down list correctly, but when I select an item it does not show in the ComboBox value. Is this a data type issue? – Ira Banks Nov 10 '20 at 14:52
There was a mistake: I forgot the `1`. instead of `Private Sub ComboBox_DropButtonClick()` use `Private Sub ComboBox1_DropButtonClick()`. Sorry. Your issue might be resolved by toggling `Application.EnableEvents`. See [this](https://stackoverflow.com/questions/12065420/populate-a-combobox-in-dropbuttonclick). – VBasic2008 Nov 10 '20 at 20:14

How would I loop through table headers to populate a ComboBox drop-down list?

1 Answers1

Fill Combo Box With Table Headers