How do I populate a combo box from a column in my excel spread sheet?

Question

I have two excel spreadsheets, one has a combobox, the other one has a list of department names. I need to populate the combobox with the department names. How do I acheive this.

Do you need to use a combobox? You can achieve that with data validation as well? — Siddharth Rout, Jan 18 '13 at 05:14

bonCodigo · Accepted Answer · 2013-01-18T04:35:08.010

19

Here is a VBA Code:

Dim vArr as Variant
Dim i as Integer
vArr = WorksheetFunction.Transpose(Sheets(2).Range("A2:A10").value)
With Sheets(1).OLEObjects("ComboBox1").Object
     .Clear
     For i = Lbound(vArr) to Ubound(vArr)
        .AddItem vArr(i)
     Next i
End With

Here is the most simpler way to load the combobox, given your department range will not be empty...

Private Sub Workbook_Open()
    Sheets(1).ComboBox1.List = Sheets(2).Range("A2:A10").Value
End Sub

or within Sheet1:

Private Sub Worksheet_Activate()
    ComboBox1.List = Sheets(2).Range("A2:A10").Value
End Sub

edited Jan 18 '13 at 04:35

answered Jan 18 '13 at 04:24

bonCodigo

14,268
1
48
91

1

How would someone do this with a range along 1 row only? I need to add 5 cells from a single Row to a comboBox – Robert English Nov 12 '13 at 10:16

How do I populate a combo box from a column in my excel spread sheet?

1 Answers1

Linked