How to prepolulate the combobox list from Excel into userform?

user_2441245 0
Viewed 160

I want to take a "list of name" data sets from excel and use it for an Combobox Userform. The user uses a dropdown menu from the Combobox to make his/her selection of a name.

My problem is that I don't know how to apply the "list of name" data sets to the combobox. The range for the list of names is in cell C2:AU2 and its in a worksheet called "DoNotPrint-Names". Once the name is chosen by the user I want it to output it to a cell in "DoNotPrint-Setup" worksheet when the button "Next" is clicked.

Screenshot Part of the Names list: https://imgur.com/sqsUFmF

Screenshot of Userform: https://imgur.com/UX8ytrY

I tried the code below which asks the Userform to prepopulate cells from "DoNotPrint - Names" worksheet by transposing it first since its a single row. Not sure how to proceed afterward. 

Private Sub UserForm_Initialize()

    ComboBox1.List = WorksheetFunction.Transpose(DoNotPrint - Names.Range("C2:AU2"))

End Sub
userform vba excel
2 Answers

Select your list and give it name,

Example

enter image description here

Then load that list on your ComboBox1

Code Example

Option Explicit
Private Sub UserForm_Activate()
    Dim vList As Variant

    For Each vList In [MyList]
        Me.ComboBox1.AddItem vList
    Next

End Sub

enter image description here

user_45397090

Addendum to found solution

Just in Addition to @0m3r 's valid answer, you can use the array Approach to assign the entire named horizontal list to the comboboxe's .List property in a one liner:

Me.ComboBox1.List = Application.Transpose([myList])

Edit

Alternatively you can use the control's .Column property which seems to be rather unknown:

Me.ComboBox1.Column = [myList].Value
user_64602970
Related