Search code examples
arraysexcelvbacomboboxuserform

How to fill combobox with an Excel-row (string)?

I would like to fill a Combobox with a row (CAT, DOG, FISH, ...) in Excel

What I did so far is the following

Private Sub UserForm_Initialize()

    Dim Axis As Variant

    Axis = Rows(1)

    ComboBox1.List = Axis

End Sub

Compiling works but I can only see the first Value (for example CAT).

And if I try the following code....

Private Sub UserForm_Initialize()

    Dim Axis As Variant

    Axis = Columns(1)              '<< Columns instead of Rows

    ComboBox1.List = Axis

End Sub

.... the Combobox contains the whole Column.

I tried many things but couldn't find a solution yet.

Therefore I'm asking you guys if anybody could help me please.

Thanks

Solution

  • If you have a row and want them displayed in a single list you have to transpose

    ComboBox1.List = Application.Transpose(Sheet1.Range("A1:C1").Value)

    You can also do 

    ComboBox1.List = Array("Cat", "Dog", "Fish")

    To keep a row in a line you have to increase the column count of the combobox.