asked on

Populate Combox Options from 1st column of multi column Range

Hello All,

I have a dynamic range - myRange, which has 3 columns. my issue is ...how can i get me combobox1 to populate fields from ONLY the first column of the myRange? I am using index to try to grab the first column but its not working....

Private Sub UserForm_Initialize()

ComboBox1.Value = ""

Dim Rng As Range
Dim Cel As Range
Set Rng = (Index(myRange, , 1))

For Each Cel In Rng
ComboBox1.AddItem Cel.Value
Next

Set Rng = Nothing
Set Cel = Nothing

End Sub

myRange >>

item1 0 1
item2 1 0
item3 0 0

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

My post - for when you need to iterate through the column.

However, for loading listboxes/comboboxes, imnorie's is a great one-step solution.

Dave

Rayne

ASKER

Thank you both Inmorie and Dave for your replies, greatly appreciated

Dave - I saw that when I move the myRange to other worksheet instead of the first sheet - the combobox fills up nothing.....it that a issue?..so the code seems to work if the range is on sheet 1 but when I moved it another sheet, the combobox would not populate...
loadCombo-r2F.xls

Rayne

ASKER

so the combobox will be on sheet 1 but the myrange will be on a separate worksheet

Rayne

ASKER

Thank you all again for your help, I got stuck :)