range columns VBA fill combobox-CodePudding

im trying to edit this code to get the columns and not the Rows, i want to fill a ComboBox with colum names, but every option y try i get Error 380 Runtime RowSource problem, so i'm trying different options, and in forums this seemed to work, but i need it for columns

    ComboBox1.Value = "Please Select an employee from the dropdown"
ComboBox1.RowSource = "Sheet1!A3:A" & Range("A" & Rows.Count).End(xlUp).Row

The blue headers are the ones i need in the comboBox, from A3:J3 enter image description here

Headers in blue

Thanks!

CodePudding user response：

Have a look at this solution as a starting point for loading your data in a ComboBox: Populate a combo box with a string array in a user form

Ignore the UserForm part, as it might not be relevant in this scenario.

I recommend that you make a range object out of the column headings, load them into an array and then load them into a combo box, in the event that these headings might change at some moment.

CodePudding user response：

From the picture, it looks like your data is in a List Object. If so, it is very easy to grab the Headers from the list object, and turn them into an array. Once you have an array, you can insert the array into the ComboBox List like myComboBox.List = myArray.

Sub Example()
    'Get the List Object
    Dim myList As ListObject
    Set myList = Sheet1.ListObjects("List1") 'CHANGE "List1" TO MATCH YOUR TABLE'S NAME
    
    'Get the Headers
    Dim ListHeaders As Range
    Set ListHeaders = myList.HeaderRowRange
    
    'Create an empty Array
    Dim Headers() As String
    ReDim Headers(ListHeaders.Cells.Count - 1)
    
    'Fill the Array
    Dim i As Long
    For i = 0 To ListHeaders.Cells.Count - 1
        Headers(i) = ListHeaders.Cells(i   1)
    Next
    
    'Put the Array into the ComboBox
    Me.ComboBox1.List = Headers
    
End Sub