Populating UserForm ComboBoxes with sheet entries

[GalmOne]

I think I've literally tried everything and have come up dry. Most of the examples out there show code, but I'm just guessing at the syntax because they never explain explicitly what the names of their sheets are.

I've got a UserForm called UserForm1 with just 2 ComboBoxes - ComboBox1 and ComboBox2. I also have a sheet named "LookUpLists" with 2 columns containing entries.

Column A holds the features of a part that's being analysed (i.e. bushing, pin, rod, etc.) and column B holds what kind dimension of the feature is being analyzed in (i.e., outer diameter, inner diameter, length etc..). I did not put any headings in these two columns (i.e. the information begins with Cells A1 for features and B1 for dimensions).

I simply want to put the features into ComboBox1 and the dimensions in ComboBox2 so that when the user clicks the drop-down button on the form on either ComboBox, the lists will show.

I just don't know where to start since I've pretty tried everything I've found on the web and the drop down lists are ALWAYS empty when I drop them down in the UserForm.

[wallyeye]

I created this LoadCombo routine for my spreadsheets:

Please Login or Register  to view this content.

You would call it from your userform_initialize event, passing it the combo name and the named range of the list you are populating the combo with:

call LoadCombo(me.combobox1, "LookupList1")

Where you have named the range of your first list LookupList1.

[GalmOne]

Sorry I haven't yet tried this. I'll get to it as soon as possible and see if it works. Thanks.

[GalmOne]

Alright so I found a quick way to populate the ComboBox... Now I must figure out a way to use a loop to populate 19 more boxes with the same list in each one. The code I used for one box is shown below and I can't seem to find a way to loop this to populate the other 19 boxes.

Please Login or Register  to view this content.

[tigeravatar]

GalmOne,

If every single ComboBox on the userform needs that list, and there are no other comboboxes on the userform, then this code should work for you:

Please Login or Register  to view this content.

[tigeravatar]

wallyeye,

As a side note, your LoadCombo routine can be simplified to the following:

Please Login or Register  to view this content.

[wallyeye]

tigerav -

Cool! I'll update my codebase. I notice your code didn't specify the row count, I've seen that before but wasn't comfortable with it. I'll play with that a bit as well.

[wallyeye]

GalmOne -

If you have different combo box lists, you can use the Tag property to identify which ones you want to use the same list on. Simply select each one in the userform, then put a value in the tag property. In tigerav's initialize code, check the tag before updating the list:

if ctrl.Tag = "L1" then

[GalmOne]

Thanks all! I used everyone's suggestions and got it all working.