CODEWITHSUNDEEP
excelvbauserform
GoldFusion
GoldFusion

Reputation: 149

How to prepolulate the combobox list from Excel into userform?

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://i.sstatic.net/KneaD.jpg

Screenshot of Userform: https://i.sstatic.net/f6FGL.jpg

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

Upvotes: 3

Views: 348

Answers (2)

T.M.
T.M.

Reputation: 9948

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

Upvotes: 1

0m3r
0m3r

Reputation: 12499

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

Upvotes: 3

Related Questions