CODEWITHSUNDEEP
arraysexcelvbacomboboxuserform
Ayrix
Ayrix

Reputation: 491

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

Upvotes: 0

Views: 1313

Answers (3)

Ayrix
Ayrix

Reputation: 491

What works for me is: 

Dim axis as Variant
Dim lstColumn As Long

'Find last Column
    With ActiveSheet.UsedRange
        lstColumn = .Columns(.Columns.Count).Column
    End With

'Fill axis with all values from the first row
axis = Application.ActiveSheet.Range(Cells(1, 1), Cells(1, lstColumn))

'Write all values of axis in Combobox
ComboBox11.Column = axis

Upvotes: 1

Andrew7
Andrew7

Reputation: 1

I prefer to use following code so that I could easily remove or add the listed item on the reference table.

Dim wS As Worksheet
Dim refTable As Range

Set wS = Sheet1    
Set refTable = wS.Range("A1" , Cells(Rows.Count, "A").End(xlUp))
Combobox.List = refTable.Value

You could change the refTable range to change "A1" and "A". Also it could be used for the row to change Rows.Count and xlUp.

Upvotes: 0

SJR
SJR

Reputation: 23081

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.

Upvotes: 2

Related Questions