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