Populating multi column combobox in excel vba

Question

Hi I'm newbie in vba excel, but i didn't find yet what i'm looking for in google.

I want a list like this in my combo box

BUS     B
APPLE   A
SUGAR   S
JELLY   J

I do like this in the past

Dim listEntries(3, 2) As Variant

listEntries(0, 0) = "A"
listEntries(0, 1) = "Apple"
listEntries(1, 0) = "S"
listEntries(1, 1) = "Sugar"
listEntries(2, 0) = "J"
listEntries(2, 1) = "Jelly"

Me.ComboBox1.List = listEntries

but now days the data become so many. I want the list is coming from another sheet list. I found this in Google. but it is still not works

Private Sub UserForm_Initialize()
Dim cItem As Range
Dim ws As Worksheet

Set ws = Worksheets("LookupLists")

For Each cItem In ws.Range("ItemList")
  With Me.cboItem
    .AddItem cItem.Value
    .List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
  End With
Next cItem
End Sub

Is there any missed in my code or is there another way to get the multiple value from another sheet ?

Rory · Accepted Answer · 2015-03-05 08:10:13Z

1

You don't need to loop if you just want to put a range in the list:

Private Sub UserForm_Initialize()

Me.cboItem.List = Worksheets("LookupLists").Range("ItemList").Resize(, 2).Value

End Sub

answered Mar 5, 2015 at 8:10

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rory Over a year ago

Please be specific - what happens exactly? (I use this sort of code all the time)

Ras Rass Over a year ago

Thank you very much... yes it is work. I was typo when type UserForm_Initialize() i typed UserForms_Initialize()...

Ras Rass Over a year ago

Thank you very much... yes it is work. I was typo when type UserForm_Initialize() i typed UserForms_Initialize()...

Collectives™ on Stack Overflow

Populating multi column combobox in excel vba

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related