Search code examples
excelvbacomboboxlistobjectexcel-tables

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


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.


Solution

  • 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