How to populate a combo box from a column in my extended excel sheet?

Question

I have two Excel tables, I have a combo box, the other is a list of department names. I need to fill in a combo box with department names. How to do it.

Answer 1

Here is the 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 easiest way to load combobox, given that the range of your department will not be empty ...

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

or inside Sheet1:

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