Link to home
Create AccountLog in
Avatar of Rayne
RayneFlag for United States of America

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
Avatar of Norie
Norie

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Avatar of 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
Avatar of Rayne

ASKER

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

ASKER

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