Excel VBA: Why does combo box shows only one item in the list?

Question

I tried to make a combo box via VBA which will show in its drop down list a number of values in certain cells from worksheet. This is the relevant code:

Set header = ActiveWorkbook.Worksheets(source_sheet_1_name).Range(Cells(4, 4), Cells(4, 9))
ActiveWorkbook.Names.Add Name:="header", RefersTo:=header
UserForm2.ComboBox1.RowSource = "header"

However, combo box shows only the first item in the list. I looked into it for a day and still could not figure out what I did wrong.

Answer 1

RowSource won't work with a horizontal list, but you can simply assign the range to the Column property of the combobox like this:

UserForm2.ComboBox1.Column = Header.Value

Answer 2

Little workaround:

Set header = ActiveWorkbook.Worksheets(source_sheet_1_name).Range(Cells(4, 4), Cells(4, 9))
For Each cell In header
     UserForm2.ComboBox1.AddItem (cell.Value)
Next cell

Answer 3

I created a vertical list and slightly tweaked the code. The form I created has a commandbutton and a combobox. clicking on the button sets the rowsource for the combobox (cbo1).

Option Explicit
Dim mylist As Range

Private Sub CommandButton1_Click()
Set mylist = ActiveWorkbook.Worksheets("lists").Range(Cells(4, 4), Cells(9, 4))
ActiveWorkbook.Names.Add Name:="header", RefersTo:=mylist
Me.cbo1.RowSource = "header"
End Sub

Hope this helps

Sybolt